Using a formula/variable for col_index_num in VLOOKUP

[ReggaeYT]

So the VLOOKUP formula is straightforward: (lookup_value, range, col_index_num, FALSE)

My question is: if I have a cell that has the value I want to put in the col_index_num, is there any way to do it? I've been looking online for a while and I've found answers that are similar but not the same.

ex: VLOOKUP(B2, C:E, A2, 0)

The A2 obviously returns a #VALUE error, but wondering if there's some kind of workaround I can use. This is for a rolling window VLOOKUP against a pivot table

Thanks in advance!

 

[Mr_Phil]

Hi. I think this link will take you to a tutorial that does a good job of exampling advanced vlookups.

 

[kevin9999]

I can't replicate the problem you're having.

Book1
	A	B	C	D	E
1	Col_Index	Value	HDR1	HDR2	HDR3
2	2	apple	apple	apple 5	apple 6
3			banana	banana 5	banana 6
4
5	apple 5	=VLOOKUP(B2,C:E,A2,0)
Sheet2

Cell Formulas
Range	Formula
A5	A5	=VLOOKUP(B2,C:E,A2,0)
B5	B5	=FORMULATEXT(A5)

Could you share your sheet using the XL2BB - Excel Range to BBCode