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

user_11462274 0
Viewed 913

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

google-sheets-formula google-sheets
4 Answers

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

user_114622740

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

user_253980

Adding headers to your data can be a quick fix and would allow you to go left or right.

=INDEX($A$1:$B$6,MATCH(E5,$B$1:$B$6,0),MATCH(D5,$A$1:$B$1,0))
=INDEX(Index('entire data set'), MATCH('TTT', column where 'TTT' is,0),match('Your data header along row 1')

your data with headers

user_113435640
user_20238420
Related