Vlookup to search 3rd column instead of fitst

[mordecai98]

I have a table with 4 columns of data. to lookup from the first column and return from the third, I use: =VLOOKUP(B9,Table1,3,0). If I want to lookup from the fourth column and return the balue in the third column, how would I do this?
thanks

[tom1977]

You should use combination of two funtions: MATCH and INDEX

send the attachment and I will show You how to do this

[mordecai98]

What is the formula? =match(index(B9,Table1,3,0)) or something like that?

[tom1977]

first index then match

[mordecai98]

I still cant get it to return the correct value. Based on this formula =VLOOKUP(B9,Table1,3,0) In table1 how do I write it? How do I attach the file?
thx

[mordecai98]

I see it. Is it within the vlookup formula?

[tom1977]

I do not follow...
please click Go Advanced icon and then Paper Clip icon an attach the file with description of your problem

[daddylonglegs]

You can get the nth column from a multi-column range with INDEX like this

INDEX(Range,0,n)

...therefore to lookup a value in the 4th column and return a value from 3rd you can use this version

=INDEX(INDEX(Table1,0,3),MATCH(B9,INDEX(Table1,0,4),0))

[mordecai98]

I now get the #N/A error. If b9 is blank then it returns a value from the correct column.

[daddylonglegs]

If you get #N/A with the formula I suggested then that probably means that B9 does't exist in the 4th column of the named range - are you sure there's an exact match?

[mordecai98]

Here is my version. See sheet1. B16 & C16.
thanks

[popipipo]

=INDEX(Index!B3:B3600;MATCH(B16;Index!C3:C3600;0))

I think this works

[tom1977]

look at attachment

[daddylonglegs]

You said you wanted to look up a value in the 4th column and return a value from the 3rd. That formula looks up the value in the 3rd column and returns a value from the 2nd.....

If you want to to that while referring to parts of the named range rather than specific columns then that would be as I suggested before except with a 3 in place of the 4 and a 2 in place of the 3, i.e.

=INDEX(INDEX(Table1,0,2),MATCH(B16,INDEX(Table1,0,3),0))