Question description:
This user has given permission to use the problem statement for this
blog.
Need help with lookup when you have multiple lookup values
Solved by A. D. in 37 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
24/09/2018 - 11:23
Thank you for contacting GotIt Pro ExcelChat!
Excelchat Expert
24/09/2018 - 11:23
I understand that you need help with multiple lookup values, right?
Excelchat Expert
24/09/2018 - 11:24
If you can read this but are unable to reply, please try to refresh your browser.
Excelchat Expert
24/09/2018 - 11:26
If you are still unable to reply then your network may be partially blocking us. I'd recommend contacting our support team for help on this one. Meanwhile, I'll be giving you the solution on how to do multiple lookups so if you can read this, you may want to stay while I work on this.
Excelchat Expert
24/09/2018 - 11:26
I'll be working on the sheet to the right so you can also type something in there to communicate with me.
Excelchat Expert
24/09/2018 - 11:29
I've written 2 variations of the a multiple lookup formula in columns G and H.
Excelchat Expert
24/09/2018 - 11:29
They are both Array formulas so they need to be confirmed using CTRL+SHIFT+ENTER instead of just ENTER.
Excelchat Expert
24/09/2018 - 11:30
You'll know that they've been entered correct if they formulas are automatically enclosed in { }
Excelchat Expert
24/09/2018 - 11:30
Or arrayformula() when working with Google Sheets.
Excelchat Expert
24/09/2018 - 11:30
Here's a copy of the file that works for Excel.
[Uploaded an Excel file]
Excelchat Expert
24/09/2018 - 11:31
THe file on the right is for Google Sheets. They both behave the same.
Excelchat Expert
24/09/2018 - 11:31
Here's the first formula that works after confirming with CTRL+SHIFT+ENTER
Excelchat Expert
24/09/2018 - 11:31
=INDEX($C$1:$C$7,MATCH(E2&F2,$A$1:$A$7&$B$1:$B$7,0))
Excelchat Expert
24/09/2018 - 11:31
This combines E2 and F2 to lookup for a combination of column A and column B.
Excelchat Expert
24/09/2018 - 11:32
The other formula is a bit more complicated but can be adjusted to return multiple results:
Excelchat Expert
24/09/2018 - 11:32
=INDEX($C$1:$C$7, SMALL(IF(($A$1:$A$7=E2)*($B$1:$B$7=F2),ROW($C$1:$C$7),FALSE),1))
Excelchat Expert
24/09/2018 - 11:32
The 1 at the end can be changed to 2 to return the second match it sees.
Excelchat Expert
24/09/2018 - 11:32
Both will require CTRL+SHIFT+ENTER.
Excelchat Expert
24/09/2018 - 11:33
I sincerely hope that when you come back, you will see the solution I gave you so this session can be rated fairly.
Excelchat Expert
24/09/2018 - 11:33
Contact our support team for assistance with the connectivity issue with our platform:
Excelchat Expert
24/09/2018 - 11:33
sheets.support@gotitapp.co
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
