Lookup within a VLookup in Execl

[acct98]

Has any tried to use a lookup function within a vlookup functions?

 

[bluedragon2]

In what respect, what are you trying to do?



Blue

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Here is an example of a vlookup within a vlookup:

=VLOOKUP(VLOOKUP(A7,A1:B4,2,FALSE),E1:F4,2,FALSE)

it will lookup Cell A7 in array A1:B4 for the resul in the 2nd Column and take that value and lookup in array E1:F4 for the result in the Second Column.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[acct98]

=VLOOKUP(S2,LOOKUP(T2,H2:H25)A2:N25,8,0)
I know this formula/syntax is wrong

 

[bluedragon2]

So,

You want to lookup value S2 in Array A2:N25 for an exact match and have the result comoe from column 8.

What is the lookup of T2 in list H2:H25 supposed to be?
I do not know the reason to lookup this value...



Blue

If I wasn't Blue, I would just be a Dragon...

 

[acct98]

t2 contains actual time
i.e
time
minutes points
5 10
6 8


the problem comes in when someone runs a time between 5 and 6 minutes thus I will not have an exact match

 

[acct98]

excel wants to do the following
=VLOOKUP(VLOOKUP(T2,G2:G25,H2:H25)*A2:N25,8,FALSE)

 

[bluedragon2]

OK, I am getting confused...


You have T2 and S2

You say T2 is a time.

Now, are you wanting to lookup this time in an array to get the point value?

Also, What is S2 and what are you trying to lookup in two arrays?



Blue

If I wasn't Blue, I would just be a Dragon...

 

[acct98]

s2 would be the age of the person and there gender

 

[bluedragon2]

OK, Lets try this...

What result are you looking to get?

and

What is your criteria?

Blue

If I wasn't Blue, I would just be a Dragon...

 

[acct98]

I am looking the corresponding points according to the persons actual time, gender and age. The table that has the time and the respective points contains all ages and time.

 

[bluedragon2]

OK, now that makes more sense...

Now, I am going based that you have three criteria and the array looks somethin like:

age gender time pts etc
10 m 5 6 A
10 f 5 7 A
10 m 6 5 B
10 f 6 6 B

Now, lets say that is your array. Now you have the criteria in cells R2 (age), S2 (gender), T2 (Actual Time)...

So, we have

R2 is 10
S2 is f
T2 is 5:20

Based that you want to round down to the nearest minute, you need three cells that will put the data into the format for a DGET. So, lets say in cells R4,S4,T4 put the column headings age,gender,time. In:
R5, put =R2
S5 put =S2
T5 put =MINUTE(T2)

and in the cell you want to lookup the information, put:

=DGET(<Main array>,<Column Number that the points are in>,R4:T5)



Blue

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

Could you not just use TRUE as the 4th aegument in the vlookup to get the nearest number of minutes instead of FALSE which gives an exact match ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244