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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Antonio - 24 Oct 2006 20:10 GMT

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

Reply to this Message

JLeoni - 24 Oct 2006 20:43 GMT

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

> Hi to everyone,
> I have a document with a table with various columns, and i would need to
[quoted text clipped - 6 lines]
> Thanks in advance for any help,
> Antonio

Reply to this Message

JLeoni - 24 Oct 2006 20:45 GMT

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

sorry about that forgot to paste the url into it.

> Try using the =INDEX - MATCH formula With that you can choose which column
> to return the value from - SEE this example
[quoted text clipped - 11 lines]
> > Thanks in advance for any help,
> > Antonio

Reply to this Message

Antonio - 24 Oct 2006 21:24 GMT

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

> http://www.mrexcel.com/tip021.shtml
>
[quoted text clipped - 15 lines]
> > > Thanks in advance for any help,
> > > Antonio

Reply to this Message

JLeoni - 24 Oct 2006 20:44 GMT

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

> Hi to everyone,
> I have a document with a table with various columns, and i would need to
[quoted text clipped - 6 lines]
> Thanks in advance for any help,
> Antonio

Reply to this Message