vlookup value not in the first column

[lezawang]

Hello there
I understand that the vlookup value has to be in the first column of the table array. What is the solution if I want to use the Vlookup and the value is in the second or third column? Any work around this? Thank you so much.

 

[sunny102]

The other way is to start the array in Vlookup from column B or column C, it is not necessary to start from A only, alternatively you could also use, index match combination instead of vlookup.

 

[petrcz]

Hi, please share an example of your data.
This can be done by using index/match formula.
Thank you
RP

 

[lezawang]

Thanks for your reply. I tried to create table like this

Name PhoneNumber Address CityName

Hi, please share an example of your data.
This can be done by using index/match formula.
Thank you
RP

 

[Aladin Akyurek]

Hello there
I understand that the vlookup value has to be in the first column of the table array. What is the solution if I want to use the Vlookup and the value is in the second or third column? Any work around this? Thank you so much.

1. Control+shift+enter, not just enter:

=VLOOKUP(X2,CHOOSE({1,2},$C$2:$C$10,$A$2:$A$10),2,0)

This formula looks up X2 in C2:C10 and returns a result fro A2:A10 if successful.

2. The following is a general approach and equivalent to (1)...

=INDEX($A$2:$A$10,MATCH(X2,$C$2:$C$10,0))

which needs just enter.

 

[Wad Mabbit]

Really nice, thank you.

Which of the two methods uses resources (i.e. is INDEX or MATCH volatile and requires Excel to refresh every referenced cell etc.)

 

[navic]

Volatile Excel Functions

 

[Wad Mabbit]

Thanks, so is INDFEX volatile, or not?

"A number of functions that are documented by Microsoft as volatile do not actually seem to be volatile when tested:
INDEX(), ROWS(), COLUMNS(), AREAS()"

I guess #1 is more of a resource hog, as it is an array, right?