Try this:

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

Copy down as needed.

--
Biff
Microsoft Excel MVP


"Rj" wrote in message
...
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.