Spreadsheet VLOOKUP: Search in second column and return first

Question

In Excel or OpenOffice calc I have

| Foo | Bar |
+-----+-----+
| A   | 1   |
| B   | 2   |

Is it possible to use VLOOKUP to search a value in the Bar column and return the value in the Foo column?

Solution

Try this solution:

=INDEX(A:B,MATCH(2,B:B,0),1)

This chooses a specific cell out of a matrix.

MATCH retrieves the row inside the matrix B:B where "2" was found with exact match (0).

INDEX uses this row, and a given column (1), to refer to a specific cell inside a matrix A:B.

"Foo" would be in column A, "Bar" would be column B.

Comments

[AndrewC]

No it isn't, which is why Jook's answer doesn't use it. VLOOKUP can only use the left hand column.

[pensono]

Possible duplicate of: superuser.com/questions/645039/…

[FiddlingAway]

+1 Also working in Google Sheets.