How do I lookup from a table

[Rj]

I am trying to do a lookup that looks like this.
SHEET1
A B C
1 7000 AAA Need Formula to = 'RED'
2 8000 BBB Need Formula to = 'RED'
3 7000 BBB Need formula to = 'BLUE'
4 6000 CCC Need formula to = 'GREEN'
5 7000 CCC Need formula to = 'WHITE'

SHEET 2
A B C D E F
1 AAA BBB CCC
2 5000 GREEN 4500 WHITE 4100 YELLOW
3 6000 WHITE 5500 YELLOW 6000 GREEN
4 7000 RED 7000 BLUE 7000 WHITE
5 8000 YELLOW 8000 RED 8500 RED

I am trying to write a formula for C1:C5 in SHEET 1 to automatically find
the colors using the ARRAY A1:F5 of SHEET 2. For instance, 7000 under AAA =
Red, but 7000 under BBB = BLUE.

 

[Luke M]

=VLOOKUP(A1,INDIRECT("'Sheet2'!R1C"&MATCH(B1,Sheet2!$A$1:$F$1)&":R5C"&MATCH(B1,Sheet2!$A$1:$F$1)+1,FALSE),2,FALSE)

If your array is actually larger, change the number in "R5C" to whatever row
it needs to be.

 

[T. Valko]

Try this:

=VLOOKUP(A1,Sheet2!F$2:F$5:INDEX(Sheet2!A$2:F$5,,MATCH(B1,Sheet2!A$1:F$1,0)),2,0)

Copy down as needed.

 

[Ashish Mathur]

Hi,

You can also try the following:

1. On sheet 2, highlight A2:B5 and press Ctrl+F3 - assign it a name, say
AAA. Please do the same for C25 and E2:F5 as well. Assign BBB and CCC;
2. Now in cell C1 of sheet 1, you can use the following VLOOKUP() formula
=vlookup(A1,indirect(B1),2,0)

Please ensure that the names in column B of sheet1 are the same as the names
defined on sheet 2.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com