Microsoft Excel - How so I return a cell value from a table dpendant on 2 interchangeable cells - VLOOKUP?

Asked By Torey on 14-Mar-13 03:03 AM
Hi,

I have 2 cells J4 which can return a value of A or B and cell J5 which can return a range of numbers 1, 2, 3, 4, 5 for example.

I then have a table from cells U1 to AL38 comprising data such as;

A
1  10  20  30  40  50
2  20  30  40  50  60
3  30  40  50  60  70


B
1  20  30  40  50  60
2  30  40  50  60  70
3  40  50  60  70  80

Lets say J4 returns the value of A and J5 returns the value of 2 and I want to look up column 3 in the above table example to get a result of 30.....how do I do that? I also need the formula to pick up any number of combinations in that table based on the ever changing results in cells J4 & J5?

Thanks in advance for your help. :-)

Torey  
Harry Boughen replied to Torey on 17-Mar-13 02:06 AM
Hello Torey,
Try

=OFFSET($U$1,MATCH($J$4,$U$1:$U$38)-1+$J$5,$J$5)

Regards
Harry