Vertical VLOOKUP

[RodneyW]

I'm not sure this is a VLOOKUP but here's what I'm trying to do

I have a value in cell D42, lets say that value is "green"

I need the formula to look for D42 (green) in I2:AE2. Lets say the value green is found in K2. If so, I'd like the result of the formula to display the value in K3. If on the other hand, the value green is found in V2, the result of the formula should display the value in V3.

Make sense?

Thanks in advance

 

[AhoyNC]

Try:
INDEX(I3:AE3,MATCH(D42,I2:AE2,0))

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2					3		red	blue	green	yellow
3							1	2	3	4	5	6
4
42		green
Sheet1

Cell Formulas
Range	Formula
G2	G2	=INDEX(I3:AE3,MATCH(D42,I2:AE2,0))

 

[RodneyW]

That worked perfectly, thank you

 

[Peter_SSs]

I'm not sure this is a VLOOKUP

I'm not suggesting anything wrong with INDEX/MATCH as suggested but since you mentioned VLOOKUP I'm letting you know that there is also the single-function HLOOKUP that would work for this job.

24 11 10.xlsm
	D	E	F	G	H	I	J	K	L
1
2				3		red	blue	green	yellow
3						1	2	3	4
42	green
HLOOKUP

Cell Formulas
Range	Formula
G2	G2	=HLOOKUP(D42,I2:AE3,2,0)

 

[RodneyW]

I'm not suggesting anything wrong with INDEX/MATCH as suggested but since you mentioned VLOOKUP I'm letting you know that there is also the single-function HLOOKUP that would work for this job.

24 11 10.xlsm
	D	E	F	G	H	I	J	K	L
1
2				3		red	blue	green	yellow
3						1	2	3	4
42	green
HLOOKUP

Cell Formulas
Range	Formula
G2	G2	=HLOOKUP(D42,I2:AE3,2,0)

Good to know, thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.