Search on multiple Vlook up columns or Index on Google Sheets or Excel

0
votes

I have this data in Google Sheets

name    Id1      Id2       Id3
Simon   ID0002             ID0001   
Bob              ID003
Roger   ID004              ID005
Jim     IDO006

How can i do a multiples Vlookup or Index search to return the column name?

Searchkey   Name
ID0001     Simon
ID0002     Simon
ID003       Bob
ID004      Roger
ID005      Roger
IDO006      Jim

Heres my link : https://docs.google.com/spreadsheets/d/1VSP1OiuXTf4CxEKVC-KIKp98EU9jTsj9VOTXz7GxSC8/edit?usp=sharing

1
excelgoogle-sheets
Check my answer and your google sheet. - Harun24HR

1 Answers

4
votes

Use SUMPRODUCT() with INDEX() function. Try below-

=INDEX($A$1:$A$5,SUMPRODUCT(ROW($B$1:$D$5)*($B$1:$D$5=H4)))

enter image description here

This will work both on Excel and Google Sheet.