Excel - LOOKUP Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.