How to search a column that is not the first of the table

[Guest]

Hi to everyone,
I have a document with a table with various columns, and i would need to
search a column which is not the first, to return the corresponding value of
the first column. It would be fine to use vlookup if the column I´m searching
were the first. Is there a function which allows me to choose the column to
search? Or does vlookup has the possibility to search in another column but
the 1st?

Thanks in advance for any help,
Antonio

 

[Guest]

Try using the =INDEX - MATCH formula With that you can choose which column
to return the value from - SEE this example
http://support.microsoft.com/kb/214142 or even this one simplifies it even
more

 

[Guest]

Try using the =INDEX - MATCH formula With that you can choose which column
to return the value from - SEE this example
http://support.microsoft.com/kb/214142 or even this one simplifies it even
more

 

[Guest]

http://www.mrexcel.com/tip021.shtml

sorry about that forgot to paste the url into it.

 

[Guest]

Thanks a lot!
Now I have understood the combined use of INDEX-MATCH. Powerful and it works!
If I understand well:
INDEX(TAB;MATCH(REF;COL_N_OF_TAB;FALSE);1)
Search the reference REF in Nth column of table TAB and gives the value of
the cell in column 1 and row corresponding to the position of REF in column
N. Which is just what I needed.
And more:
INDEX(TAB;MATCH(REF;COL_1_TAB;FALSE);N)=VLOOKUP(REF;TAB;N;FALSE)
The problem is that in vlookup you can´t choose the column number to
search:it´s always 1.

Unfortunately, I had already moved the column to the first place (which
caused me a lot of trouble because of a lot of references in other
formulas...). : (

Cheers,
Antonio

"JLeoni" escreveu: