Complex 3 way lookup

shemayisroel

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
Hi,


Sample data is below.

The data table is from A1:D10

The lookup criteria is from A12:C13

What I want to do is a lookup on all the three different criteria and return all the results from Column D in the data table (Comments) I have higlighted my expected result in green and placed the actual results in D13,D14

The type of solution I'm looking for is similar to http://www.mrexcel.com/forum/showthread.php?t=362743



<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">SCORE</TD><TD style="TEXT-ALIGN: center">YEAR</TD><TD style="TEXT-ALIGN: center">DEP</TD><TD style="TEXT-ALIGN: center">Comment</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">2009</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">IT</TD><TD style="BACKGROUND-COLOR: #ccffcc">Good</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">2008</TD><TD style="TEXT-ALIGN: center">IT</TD><TD>Ok</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">2007</TD><TD style="TEXT-ALIGN: center">Mgmt</TD><TD>Not Bad</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">2008</TD><TD style="TEXT-ALIGN: center">IT</TD><TD>Thanks</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">2008</TD><TD style="TEXT-ALIGN: center">Mgmt</TD><TD>Excellent</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">2007</TD><TD style="TEXT-ALIGN: center">CC</TD><TD>Hmmm</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">CC</TD><TD>Cool</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2008</TD><TD style="TEXT-ALIGN: center">CC</TD><TD>Bad</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">2009</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">IT</TD><TD style="BACKGROUND-COLOR: #ccffcc">No Comment</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">Score Look Up</TD><TD style="TEXT-ALIGN: center">Year Look Up</TD><TD style="TEXT-ALIGN: center">Dep Look Up</TD><TD style="TEXT-ALIGN: center">RESULT</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">IT</TD><TD style="BACKGROUND-COLOR: #ccffcc">Good</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ccffcc">No Comment</TD></TR></TBODY></TABLE>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
A formula in cell D13 for example could be
=INDEX($D$2:$D$10,MATCH(A13&B13&C13,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,0),0)

That is an array formula.
You commit it to cell D13 by pressing Ctrl+Shift+Enter, not just Enter.
 
Upvote 0
Tom Urtis said:
A formula in cell D13 for example could be
=INDEX($D$2:$D$10,MATCH(A13&B13&C13,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,0),0)

That is an array formula.
You commit it to cell D13 by pressing Ctrl+Shift+Enter, not just Enter.
Click to expand...

Hi Tom,

That worked but only returned the 1st instance, when copied down it does not return any other instance. I did modify the formula to the below but the formula will only keep on returning the 1st instance of the match...

=INDEX($D$2:$D$10,MATCH($A$13&$B$13&$C$13,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,0),0)
 
Upvote 0
Well, compare my formula with your unexplained rendition of it.

In my formula,
A13&B13&C13
are relative

and in your formula
$A$13&$B$13&$C$13
are absolute

My formula will do what you want if you simply enter it as I posted and drag it down.

Also, I wonder what you are expecting as a different return value for that "No comment" example in cell D14, when the elements in range A2:C2 are the same as A10:C10.
 
Upvote 0
Tom Urtis said:
Also, I wonder what you are expecting as a different return value for that "No comment" example in cell D14, when the elements in range A2:C2 are the same as A10:C10.
Click to expand...

Yes this is what I'm looking for, to return all the match values where the conditions are met. Very similar function to what an auto filter will do (but of course the auto filter will just hide the lines for you) but via a formula solution.

There is below a similar post with the solution but this was based on only 1 match criteria...

http://www.mrexcel.com/forum/showthread.php?t=362743
 
Upvote 0
try this,

D13
Code: 
=IF(SUMPRODUCT(--($A$2:$A$10=$A$13),--($B$2:$B$10=$B$13),--($C$2:$C$10=$C$13))>=ROWS(D$13:D13),INDEX($D$2:$D$10,LARGE(($A$2:$A$10=$A$13)*($B$2:$B$10=$B$13)*($C$2:$C$10=$C$13)*ROW($D$2:$D$10),ROWS(D$13:D13))-ROW($D$2)+1),"")

with CSE, and copied down
 
Upvote 0
This can be done with array formulae as suggested by others. Example solution below.
The result formula in D13 can be copied down along with the helper cells in E13:F13. You can apply conditional formating to the results table so that only the cells <> "" have the green background.

Excel Workbook
ABCDEF
1SCOREYEARDEPComment
232009ITGood
352008ITOk
462007MgmtNot Bad
562008ITThanks
682008MgmtExcellent
782007CCHmmm
832009CCCool
932008CCBad
1032009ITNo Comment
112
12Score Look UpYear Look UpDep Look UpRESULTCountOffset
1332009ITGood10
14No Comment28
15  
16  
17  
Sheet1
 
Upvote 0
sanrv1f said:
try this,

D13
Code: 
=IF(SUMPRODUCT(--($A$2:$A$10=$A$13),--($B$2:$B$10=$B$13),--($C$2:$C$10=$C$13))>=ROWS(D$13:D13),INDEX($D$2:$D$10,LARGE(($A$2:$A$10=$A$13)*($B$2:$B$10=$B$13)*($C$2:$C$10=$C$13)*ROW($D$2:$D$10),ROWS(D$13:D13))-ROW($D$2)+1),"")

with CSE, and copied down
Click to expand...

THANK YOU - again you provide me with a supernatural solution so thank you very much.

I was wondering, the solution returns the last instance 1st then works it's way up (bottom up approach), I was wondering if there was any way that could be changed to be the opposite?
Not a big deal but if not then no problem. If not no big deal as it still is what I was looking for.
 
Upvote 0
You can do this but in my opinion this situation was what AutoFilter or Advanced Filter was made for.

Let's say your list of bona fide values is 3 columns deep, meaning cells D13, D14, and D15 contain these values, in reverse order as you noticed.

In cell E13 you can put in
=INDEX($D$13:$D$15,ROWS(13:$15))
again, with Ctrl+Shift+Enter
and drag down to E15.

That will reverse the reverse order, but I'd still opt for the Filter route.
 
Upvote 0
How about this solution

Excel Workbook
ABCDE
1SCOREYEARDEPComment
232009ITGood
352008ITOk
462007MgmtNot Bad
562008ITThanks
682008MgmtExcellent
782007CCHmmm
832009CCCool
932008CCBad
1032009ITNo Comment
11
12Score Look UpYear Look UpDep Look UpRESULT
1362008ITThanks1
14 
15 
16 
17
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
242
Cerka
C
E
  • Question Question
Adding a custom message.
Replies
1
Views
633
Micron
M
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
79
Dazzybeeguy
D

Forum statistics

Threads
1,223,247
Messages
6,171,007
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top