Search code examples
google-sheetsgoogle-sheets-formula

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

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

Solution

  • 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)))