3 way lookup

shemayisroel

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


Sample data below

Formula result im looking for in the below data is to

1) Lookup value in Column A being D

2) Lookup value in Column B being 4

3) Lookup value in row C1:G1

Return result being in E5

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><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><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">1st TYPE</TD><TD style="FONT-WEIGHT: bold">2nd TYPE</TD><TD style="FONT-WEIGHT: bold">NAME 1</TD><TD style="FONT-WEIGHT: bold">NAME 2</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffcc">NAME 3</TD><TD style="FONT-WEIGHT: bold">NAME 4</TD><TD style="FONT-WEIGHT: bold">NAME 5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Item1</TD><TD>Item2</TD><TD>Item3</TD><TD>Item4</TD><TD>Item5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">2</TD><TD>item6</TD><TD>item7</TD><TD>item8</TD><TD>item9</TD><TD>item10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">C</TD><TD style="TEXT-ALIGN: center">3</TD><TD>item11</TD><TD>item12</TD><TD>item13</TD><TD>item14</TD><TD>item15</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">D</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">4</TD><TD>Item1</TD><TD>Item2</TD><TD style="BACKGROUND-COLOR: #ffff99">Item3</TD><TD>Item4</TD><TD>Item5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center">5</TD><TD>item6</TD><TD>item7</TD><TD>item8</TD><TD>item9</TD><TD>item10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">F</TD><TD style="TEXT-ALIGN: center">6</TD><TD>item11</TD><TD>item12</TD><TD>item13</TD><TD>item14</TD><TD>item15</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">G</TD><TD style="TEXT-ALIGN: center">7</TD><TD>item16</TD><TD>item17</TD><TD>item18</TD><TD>item19</TD><TD>item20</TD></TR></TBODY></TABLE>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
If I understand what you want, I think this will work:
Excel Workbook
ABCDEFGHIJKL
11st Type2nd TypeName1Name2Name3Name4Name51st Type2nd TypeNameResult
2A1Item1Item2Item3Item4Item5D4Name3Item3
3B2item6item7item8item9item10
4C3item11item12item13item14item15
5D4Item1Item2Item3Item4Item5
6E5item6item7item8item9item10
7F6item11item12item13item14item15
8G7item16item17item18item19item20
Sheet13
 
Upvote 0
Try...

I2: D

J2: 4

K2: NAME 3

L2, control+shift+enter, not just enter:
Rich (BB code):
=INDEX($C$2:$G$8,
    MATCH(I2,IF($B$2:$B$8=J2,$A$2:$A$8),0),
    MATCH(K2,$C$1:$G$1,0))
 
Upvote 0
Try...

I2: D

J2: 4

K2: NAME 3

L2, control+shift+enter, not just enter:
Rich (BB code):
=INDEX($C$2:$G$8,
  MATCH(I2,IF($B$2:$B$8=J2,$A$2:$A$8),0),
  MATCH(K2,$C$1:$G$1,0))

Thanks again Aladin, was hoping for a solution exc SUMPRODUCT hence my origional post, this is exactly what I was hoping to find.

Thank you <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>
 
Upvote 0
Another option may be to use a helper column (could be hidden) then use a standard INDEX/MATCH without either Sumproduct or Array formula. H2 copied down.

Excel Workbook
ABCDEFGHIJKL
11st TYPE2nd TYPENAME 1NAME 2NAME 3NAME 4NAME 51st TYPE2nd TYPENameResult
2A1Item1Item2Item3Item4Item5A|1D4Name 3Item3
3B2item6item7item8item9item10B|2
4C3item11item12item13item14item15C|3
5D4Item1Item2Item3Item4Item5D|4
6E5item6item7item8item9item10E|5
7F6item11item12item13item14item15F|6
8G7item16item17item18item19item20G|7
INDEX MATCH
 
Upvote 0
this is EXACTLY what i'm trying to do, but i'm trying to do it by referencing all the data from a different worksheet. I'm trying to integrate the INDIRECT function into this formula to accomplish it, but not having much success. Can anyone help? thanks in advance!

Another option may be to use a helper column (could be hidden) then use a standard INDEX/MATCH without either Sumproduct or Array formula. H2 copied down.

INDEX MATCH

*ABCDEFGHIJKL
11st TYPE2nd TYPENAME 1NAME 2NAME 3NAME 4NAME 5*1st TYPE2nd TYPENameResult
2A1Item1Item2Item3Item4Item5A|1D4Name 3Item3
3B2item6item7item8item9item10B|2****
4C3item11item12item13item14item15C|3****
5D4Item1Item2Item3Item4Item5D|4****
6E5item6item7item8item9item10E|5****
7F6item11item12item13item14item15F|6****
8G7item16item17item18item19item20G|7****

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
H2=A2&"|"&B2
L2=INDEX($C$2:$G$8,MATCH(I2&"|"&J2,$H$2:$H$8,0),MATCH(K2,$C$1:$G$1,0))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
this is EXACTLY what i'm trying to do, but i'm trying to do it by referencing all the data from a different worksheet. I'm trying to integrate the INDIRECT function into this formula to accomplish it, but not having much success. Can anyone help? thanks in advance!
Like this (still using a helper column)?

Formula in H2 is copied down.

Excel Workbook
ABCDEFGH
11st TYPE2nd TYPENAME 1NAME 2NAME 3NAME 4NAME 5
2A1Item1Item2Item3Item4Item5A|1
3B2item6item7item8item9item10B|2
4C3item11item12item13item14item15C|3
5D4Item1Item2Item3Item4Item5D|4
6E5item6item7item8item9item10E|5
7F6item11item12item13item14item15F|6
8G7item16item17item18item19item20G|7
9
Data Sheet




Then in the other sheet:

Excel Workbook
ABCD
11st TYPE2nd TYPENameResult
2D4Name 3Item3
Result Sheet
 
Upvote 0
Thanks for your help Peter, i'm almost there!

This is the worksheet i'm working from 'BLZR':


Cell B10 formula is: =INDEX(Inventory!$D$2:$G$41,MATCH($G$5,Inventory!$B$2:$B$41,0),MATCH(C$10&"|"&$B11,Inventory!$H$2:$H$41,1),MATCH($I$5,Inventory!$D$1:$G$1,0))
i plan on dragging it around to the rest of the open fields when i get the formula correct.

This is the data worksheet 'Inventory':
kkfpBJV.png


It appears that my formula is nearly working correctly, but it is not pulling the correct quantity from the IVQTYR field, which i think has to do with my error with the concatenated helper field. i'm just at the limit of my knowledge, i'm so grateful for your help! Please let me know if you need any additional information, and thanks again in advance! - Justin
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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