3 way lookup

[shemayisroel]

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>

 

[Peter_SSs]

I'm struggling to replicate what you have.

1. Working from images is difficult as I cannot copy/paste the sample data to my worksheets to test. Using Excel jeanie like I have, or the MrExcel HTML Maker, to post some small copyable screen shots would help.

2. The formula for B10 in your post is not the same as the B10 formula shown in your 'BLZR' screen shot. In any case, your formulas have 3 MATCH functions in them. My formula only has 2.

3. Do you have (dreaded) merged cells in 'BLZR'?

My best guess is
=INDEX(Inventory!$D$2:$G$41,MATCH($F$4&"|"&TEXT(B$9,"00")&$A10,Inventory!$H$2:$H$41,0),MATCH($H$4,Inventory!$D$1:$G$1,0))

If that is not it, can you explain in words what in 'BLZR' needs to be matched in 'Inventory', & where it appears in 'Inventory'? Give an example that uses data that is all visible in your screen shots.

 

[justindh]

Peter, thanks for all the help! With some of your suggestions and a few other tricks, i got it working! thank you very much for all your help,

Justin

I'm struggling to replicate what you have.

1. Working from images is difficult as I cannot copy/paste the sample data to my worksheets to test. Using Excel jeanie like I have, or the MrExcel HTML Maker, to post some small copyable screen shots would help.

2. The formula for B10 in your post is not the same as the B10 formula shown in your 'BLZR' screen shot. In any case, your formulas have 3 MATCH functions in them. My formula only has 2.

3. Do you have (dreaded) merged cells in 'BLZR'?

My best guess is
=INDEX(Inventory!$D$2:$G$41,MATCH($F$4&"|"&TEXT(B$9,"00")&$A10,Inventory!$H$2:$H$41,0),MATCH($H$4,Inventory!$D$1:$G$1,0))

If that is not it, can you explain in words what in 'BLZR' needs to be matched in 'Inventory', & where it appears in 'Inventory'? Give an example that uses data that is all visible in your screen shots.