Nested VLOOKUP - help please!

Hi

I'd appreciate some help on how to nest a VLOOKUP (or a better method to do the following)

In Cell BE3 I have a persons name EG 'F Smith'
in Cell BF3 I have the reference T1I1

In cell BG3 I want to look up from a table in cells A3:Q12 what matches the combination of BE3 and BF3

In the table, the peoples names are in column 4 and the result to be returned to cell BG3 will come from column 16

Hope all that makes sense.
Can't seem to get this right.

Many thanks

Post an example workbook, then it should be easier to see how your data is laid out.

Pete

OK, sample data is attached.

I've removed data from the T2I1/T2I2/T3I1/T3I2/T4I1/T4I2/T5I1/T5I2 areas, but essentially they are the same size as areas for T1I1 and T2I2
I have added Comments to Cells BG3 and BH3 to show what data I need to extract from the table.

Hopefully this is clear.
The data in the table (Columns A to U) actually goes to a pivot table, but there are 2 columns I can't create in the pivot, so this data will be looked up from the 'helper' table in columns BE:BH in the form (in the example of Mr Smith) 1-40 (IE "Wick"-"Run")

A pivot will give you the answer you require without using lookups - and can be reported on in any format you like at whatever level of detail you need. see attached.

Try this in BG3
=VLOOKUP($BE$3,OFFSET($D$3:$U$13,MATCH($BF3,$A$3:$A$40,0)-1,0),13,0)

Change the 13 to 14 for BH3

Originally Posted by MarieR

A pivot will give you the answer you require without using lookups - and can be reported on in any format you like at whatever level of detail you need. see attached.

Thanks Marie, I had thought of a pivot - I use them extensively, but I need to do some ranking on the results and just add the best result for each person as a lookup alongside my main pivot table that is derived from the main data table in my example.
IE - I do a division of wick/run for each result and rank them to arrive at the best figures to feed to the main pivot.

I had originally referenced all those cells in columns BG and BH just using ='cell' but that's messy and prone to error.

Originally Posted by Jonmo1

Try this in BG3
=VLOOKUP($BE$3,OFFSET($D$3:$U$13,MATCH($BF3,$A$3:$A$40,0)-1,0),13,0)

Change the 13 to 14 for BH3

Thank you Jonmo1 - that's perfect.

You're welcome