Multiple Criteria Lookup

[scottlarock]

Hello,

I have been trying to ask my question right, I hope it is somewhat clear this time.
I am trying to return the matching price in column L as found in column D according to H, I, J, which should match A, B, C.
The exercise consists in comparing the prices found in column D with those in column K.

Could anyone tell me what the formula would be in column L ?

I know, this sounds very easy, but I just don't have the excel logic to work this out.... All I can do is a simple 1 criteria vlookup but that doesn't suffice on the real db.

Thank you all so much for the time and support !

[TABLE="width: 944"]
<TBODY>[TR]
[TD] [/TD]
[TD] A</SPAN>[/TD]
[TD] B</SPAN>[/TD]
[TD] C</SPAN>[/TD]
[TD] D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[TD]F</SPAN>[/TD]
[TD]G</SPAN>[/TD]
[TD] H</SPAN>[/TD]
[TD] I</SPAN>[/TD]
[TD] J</SPAN>[/TD]
[TD] K</SPAN>[/TD]
[TD] L</SPAN>[/TD]
[TD] M</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]Major</SPAN>[/TD]
[TD]Item code</SPAN>[/TD]
[TD]Description</SPAN>[/TD]
[TD]Price</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Major</SPAN>[/TD]
[TD]Item code</SPAN>[/TD]
[TD] Description</SPAN>[/TD]
[TD]Price</SPAN>[/TD]
[TD]Compared price</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 1</SPAN>[/TD]
[TD]Mic Pump 1</SPAN>[/TD]
[TD]100.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic1</SPAN>[/TD]
[TD]Mic Pump 1</SPAN>[/TD]
[TD]102.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 2</SPAN>[/TD]
[TD]Mic Pump 2</SPAN>[/TD]
[TD]120.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 2</SPAN>[/TD]
[TD]Mic Pump 2</SPAN>[/TD]
[TD]122.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 3</SPAN>[/TD]
[TD]Mic Pump 3</SPAN>[/TD]
[TD]130.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 3</SPAN>[/TD]
[TD]Mic Pump 3</SPAN>[/TD]
[TD]132.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 4</SPAN>[/TD]
[TD]Mic Pump 4</SPAN>[/TD]
[TD]150.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 4</SPAN>[/TD]
[TD]Mic Pump 4</SPAN>[/TD]
[TD]152.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 5</SPAN>[/TD]
[TD]Mic Pump 5</SPAN>[/TD]
[TD]170.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 5</SPAN>[/TD]
[TD]Mic Pump 5</SPAN>[/TD]
[TD]172.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 6</SPAN>[/TD]
[TD]Mic Pump 6</SPAN>[/TD]
[TD]190.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 6</SPAN>[/TD]
[TD]Mic Pump 6</SPAN>[/TD]
[TD]192.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 7</SPAN>[/TD]
[TD]Mic Pump 7</SPAN>[/TD]
[TD]200.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 7</SPAN>[/TD]
[TD]Mic Pump 7</SPAN>[/TD]
[TD]202.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 8</SPAN>[/TD]
[TD]Mic Pump 8</SPAN>[/TD]
[TD]220.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 8</SPAN>[/TD]
[TD]Mic Pump 8</SPAN>[/TD]
[TD]222.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 9</SPAN>[/TD]
[TD]Mic Pump 9</SPAN>[/TD]
[TD]240.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 9</SPAN>[/TD]
[TD]Mic Pump 9</SPAN>[/TD]
[TD]242.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 10</SPAN>[/TD]
[TD]Mic Pump 10</SPAN>[/TD]
[TD]260.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 10</SPAN>[/TD]
[TD]Mic Pump 10</SPAN>[/TD]
[TD]262.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 11</SPAN>[/TD]
[TD]Mic Pump 11</SPAN>[/TD]
[TD]280.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 11</SPAN>[/TD]
[TD]Mic Pump 11</SPAN>[/TD]
[TD]282.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 12</SPAN>[/TD]
[TD]Mic Pump 12</SPAN>[/TD]
[TD]300.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 12</SPAN>[/TD]
[TD]Mic Pump 12</SPAN>[/TD]
[TD]302.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14</SPAN>[/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic Pump 13</SPAN>[/TD]
[TD]Mic Pump 13</SPAN>[/TD]
[TD]320.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Music</SPAN>[/TD]
[TD]Mic 13</SPAN>[/TD]
[TD]Mic Pump 13</SPAN>[/TD]
[TD]322.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 1</SPAN>[/TD]
[TD]Mic Pump 14</SPAN>[/TD]
[TD]340.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 14</SPAN>[/TD]
[TD]Vid Pump 6</SPAN>[/TD]
[TD]342.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 2</SPAN>[/TD]
[TD]Mic Pump 15</SPAN>[/TD]
[TD]360.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 15</SPAN>[/TD]
[TD]Vid Pump 7</SPAN>[/TD]
[TD]362.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 3</SPAN>[/TD]
[TD]Mic Pump 16</SPAN>[/TD]
[TD]380.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 16</SPAN>[/TD]
[TD]Vid Pump 8</SPAN>[/TD]
[TD]382.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 4</SPAN>[/TD]
[TD]Mic Pump 17</SPAN>[/TD]
[TD]400.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 17</SPAN>[/TD]
[TD]Vid Pump 9</SPAN>[/TD]
[TD]402.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 5</SPAN>[/TD]
[TD]Mic Pump 18</SPAN>[/TD]
[TD]420.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 18</SPAN>[/TD]
[TD]Vid Pump 10</SPAN>[/TD]
[TD]422.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 6</SPAN>[/TD]
[TD]Mic Pump 19</SPAN>[/TD]
[TD]440.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 19</SPAN>[/TD]
[TD]Vid Pump 11</SPAN>[/TD]
[TD]442.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 7</SPAN>[/TD]
[TD]Mic Pump 20</SPAN>[/TD]
[TD]460.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 20</SPAN>[/TD]
[TD]Vid Pump 12</SPAN>[/TD]
[TD]462.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 8</SPAN>[/TD]
[TD]Mic Pump 21</SPAN>[/TD]
[TD]480.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 21</SPAN>[/TD]
[TD]Vid Pump 1</SPAN>[/TD]
[TD]482.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 9</SPAN>[/TD]
[TD]Mic Pump 22</SPAN>[/TD]
[TD]500.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 22</SPAN>[/TD]
[TD]Vid Pump 2</SPAN>[/TD]
[TD]502.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 10</SPAN>[/TD]
[TD]Mic Pump 23</SPAN>[/TD]
[TD]520.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Mic 23</SPAN>[/TD]
[TD]Vid Pump 3</SPAN>[/TD]
[TD]522.00</SPAN>[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 11</SPAN>[/TD]
[TD]Mic Pump 24</SPAN>[/TD]
[TD]540.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26</SPAN>[/TD]
[TD]Cinema</SPAN>[/TD]
[TD]Vid Pump 12</SPAN>[/TD]
[TD]Mic Pump 25</SPAN>[/TD]
[TD]560.00</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[Andrew Poulsom]

In L2 copied down:

=INDEX(D$2:D$26,MATCH(1,INDEX(((A$2:A$26=H2)*(B$2:B$26=I2)*(C$2:C$26=J2)),),FALSE))

Note that the Item Code's in column I must match those in column B. They don't in your sample data.

 

[blossomthe2nd]

Hi Scott

You could do it a few ways - I would use a Sumproduct formula - in Cell L2 paste the following and drag

=SUMPRODUCT(--($B$2:$B$24=H2),--($C$2:$C$24=I2),--($D$2:$D$24=J2),--($E$2:$E$24))

Its basically looking at column B and when thats equal to H2 , AND Column C when thats equal to I2 AND column D when thats equal to J2 , returning that is in Column E ,

Note that the sample above will return no matches as none of the item codes match in Column b match Column I- they have to be exactly the same as each other.

And I only took sample data for you so if range was greater change all the row 24 to whatever is relevant

A

This will only work if lookup range has unique items

 

[scottlarock]

Andrew, that works like a charm ! So glad I finally got an answer... after 3 attempts... It's actually not that easy posting a thread ;°)
Take care and have a great day !

In L2 copied down:

=INDEX(D$2:D$26,MATCH(1,INDEX(((A$2:A$26=H2)*(B$2:B$26=I2)*(C$2:C$26=J2)),),FALSE))

Note that the Item Code's in column I must match those in column B. They don't in your sample data.

 

[scottlarock]

Hello "A",

Thanks a ton for that awesome formula, it works perfectly... I'm really thankful you guys are able to answer these sorts of questions !
Take care and have a great day.
Scott

Hi Scott

You could do it a few ways - I would use a Sumproduct formula - in Cell L2 paste the following and drag

=SUMPRODUCT(--($B$2:$B$24=H2),--($C$2:$C$24=I2),--($D$2:$D$24=J2),--($E$2:$E$24))

Its basically looking at column B and when thats equal to H2 , AND Column C when thats equal to I2 AND column D when thats equal to J2 , returning that is in Column E ,

Note that the sample above will return no matches as none of the item codes match in Column b match Column I- they have to be exactly the same as each other.

And I only took sample data for you so if range was greater change all the row 24 to whatever is relevant

A

This will only work if lookup range has unique items

 

[scottlarock]

By the way, I'm extra glad you were able to sort this out for me because I was trying to get those 2 formulas (your index match, and sumproduct as posted by blossomthe2nd). I was looking at the excel blog "using multiple criteria in excel lookup" written by JP Pinto on this site and he was explaining these exact formulas along with another lookup... But I swear even when you have concrete examples... those always differ slightly from case to case & I just couldn't seem to get it done.
So extra special thanks to you guys !

In L2 copied down:

=INDEX(D$2:D$26,MATCH(1,INDEX(((A$2:A$26=H2)*(B$2:B$26=I2)*(C$2:C$26=J2)),),FALSE))

Note that the Item Code's in column I must match those in column B. They don't in your sample data.