Getting the Cell Reference from a VLOOKUP

[Mattyastill]

Hi,

I'm currently trying to write the cell ref of a vlookup (M2) i've used in the cell next to it N2. This is the formula i've used for the lookup.

=VLOOKUP(F2,AxTable1[[VPN]:[Item VAT]],1,FALSE)

The idea of what im doing is bringing back an item number and then i need to bring back a quantity for that item number. However, Qty's are not unique so i would need the cell ref to search for the quantity on the same row as what the previous vlookup returned.


I've tried a combination of Address, Match and Index but can't seem to get it working.

Any help would be much appreciated.

 

[steve the fish]

These two formulas with different column index numbers:

=VLOOKUP(F2,AxTable1[[VPN]:[Item VAT]],1,FALSE)

=VLOOKUP(F2,AxTable1[[VPN]:[Item VAT]],2,FALSE)

will both return values in the table from the same row (provided they dont error of course)

 

[AlanY]

see if you can get this work for you, the '4' at the end of the formula is the column index in your vlookup table

Book1
	A	B	C	D
1			1	A
2	3	$D$3	2	B
3			3	C
4			4	D
5			5	E
Sheet1

Cell Formulas
Range	Formula
B2	=ADDRESS(MATCH(VLOOKUP(A2,C1:D5,2,0),D:D,0),4)

 

[Mattyastill]

Thanks soo much, this worked a treat. I think i was trying to make it far more complicated than it needed to be. Just a standard VLookup worked.

 

[AlanY]

you're welcome
glad that works for you