Use `VLOOKUP` to return previous column of search key (Google Sheets)

Question

I need a system that accepts working with ARRAYFORMULA, so I use VLOOKUP. Let's go to the example:

ABA   ZZZ
EBE   XXX
IBI   TTT
OBO   UUU
UBU   VVV

In that case if I wanted to look for IBI and return the next column, I would use=VLOOKUP("IBI",A1:B,2,FALSE) and it would return TTT.

Now, if so, if I wanted to search TTT to return the column before it, to get the value IBI, how should I proceed?

I tried using -1 in the index of VLOOKUP and also tried using INDIRECT("B1:A") to invert the columns, but neither option worked.

If think it is best to help directly through the spreadsheet, here I leave the link:

https://docs.google.com/spreadsheets/d/1PTmWDpD_xmI4WEIGuveduuTZGdyndc9aJd_GRPX8E4k/edit?usp=sharing

Answer 1

I was able to find the best way, for a search of only one value, it can be used as follows:

=ARRAYFORMULA(IFERROR(VLOOKUP("TTT",{B:B,A:A},2,FALSE)))

If you are looking for multiple values placed on different lines, you can use with ARRAY quietly, just changing "TTT" for the desired value column, such as the D column, would be like this :

=ARRAYFORMULA(IFERROR(VLOOKUP(D1:D,{B:B,A:A},2,FALSE)))

Answer 2

If I wanted to search TTT to return the column before it, to get the value IBI, how should I proceed?

I don't know about Google Sheets but with Excel you cant. Your data must be laid out in left to right and the index you are asking for is interpreted to be to the right. You cannot ask it to go left.

There are tricks and alternate formulas to do reverse, but a vanilla call to VLOOKUP is only left to right.

Alternatives for reverse direction: https://www.exceltip.com/lookup-formulas/reverse-vlookup-in-microsoft-excel.html