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

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

Luke M
*Remember to click "yes" if this post helped you!*


"Rj" wrote:

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.