I'm having some issues with a three way lookup. I've searched the forums and found some answers but can't get them to work. I've attached an example spreadsheet and am trying to get the "answer" field to return data using the input cells in B2:B4. Any help would be greatly appreciated!
Excel Example.xlsx
With an ARRAYED FORMULA
=INDEX(C8:K19, MATCH(B3&B2, A8:A19&B8:B19,0),MATCH(B4,C7:K7,0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
with a helpcolumn in A.
After that index / match.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
=SUM((A8:A19=B3)*(B8:B19=B2)*(INDEX(C8:K19,,MATCH(B4,C7:K7,0))))
Try that formula - you need to press Ctrl+Shift+Enter to confirm the formula.
Edit:
THis version is more friendly - it's a regular formula (no need for Ctrl+Shift+Enter)
=SUMPRODUCT((A8:A19=B3)*(B8:B19=B2)*(INDEX(C8:K19,,MATCH(B4,C7:K7,0))))
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks