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>
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,059
Office Version
365, 2010
Platform
Windows
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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))
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
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>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867
Thanks Peter, another clever way - much appreciated.
 

justindh

New Member
Joined
Jul 20, 2015
Messages
5
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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
 

justindh

New Member
Joined
Jul 20, 2015
Messages
5
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':


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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,060
Messages
5,509,067
Members
408,705
Latest member
denesh3560

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top