LinkBack URL
About LinkBacks
Hi
I want to make a to way lookup with multiple criteria to get some values in a table (see attached file).
I have tried with "index, match, match", but couldn't get i to work.
Any suggestions?
Tommy
Two way lookup with multiple criteria
Hi
I want to make a to way lookup with multiple criteria to get some values in a table (see attached file).
I have tried with "index, match, match", but couldn't get i to work.
Any suggestions?
Tommy
Kind regards
Leo
Try this regular formula, copied across...
=INDEX(C$2:C$7,MATCH($A11&" "&$B11,INDEX($A$2:$A$7&" "&$B$2:$B$7,0),0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
LeoTaxi, Sorry for off-topic interjection:
Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Thanks again for all your hard work here!
Try this...
Entered in C11 and copied across:
=SUMIFS(C2:C7,$A2:$A7,$A11,$B2:$B7,$B11)
You may have to replace the commas with semi-colons.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
tomjoe, a quick question...
Will the data always be unique, or would you need to combine duplicate values?
hmm also...Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.
Thank you for trying to help me out.
To explain it better:
If the cell A11, B11 and C10:E10 changes, the formula should still find the matching value in the table above. It should be possible to put the lookup tale in another sheet or even another workbook and the formula should still work.
I have tried with something like this ={INDEX($A$1:$N$7,MATCH($A11&$B11,$A$1:$A$7&$B$1:$B$7,0),MATCH(C$10,$C$1:$N$1,0))}. Am I overcomplicating Things?
OK, I was on right track, but hadn't all detailes right.
The formula should be ={INDEX($A$1:$N$7,MATCH($A11&$B11,$A$1:$A$7&$B$1:$B$7,0),MATCH(C$10,$A$1:$N$1,0))}
Tommy
C11=IFERROR(INDEX($C$2:$E$7,MATCH($A11&$B11,INDEX($A$2:$A$7&$B$2:$B$7,0),0),MATCH(C$10,$C$1:$E$1,0)),"")Try this and copy across
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Formula:
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Last edited by Czeslaw; 09-19-2015 at 05:29 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks