Hlookup with multiple criteria.

drewjwats

New Member
Joined
Mar 15, 2016
Messages
3
I searched online for a good amount of time and came up empty handed, I may be explaining what I am attempting to do wrong.


I am trying to return a row value based on the customer number and a "Yes" in certain columns.

For example, in the following I would be attempting to return the value of the most recent yes. Week 2, for 0001 and week 5 for 0002. I cant for the life of me figure out what the formula would be.


Cus Number Week 1 Week 2 Week 3 Week 4 Week 5
0001 No Yes No No No
0002 No Yes No Yes Yes


Any help on this would be much appreicated.


Thank you!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

drewjwats

New Member
Joined
Mar 15, 2016
Messages
3
To be more clear, I would be linking from a different spread sheet, which only has the customer number, I would be attempting to return the week of the most recent yes.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Does this help you (formula in I2 copied down)?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Cus Number</td><td style=";">Week 1</td><td style=";">Week 2</td><td style=";">Week 3</td><td style=";">Week 4</td><td style=";">Week 5</td><td style="text-align: right;;"></td><td style=";">Cus Number</td><td style=";">Week No</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0001</td><td style=";">No</td><td style=";">Yes</td><td style=";">No</td><td style=";">No</td><td style=";">No</td><td style="text-align: right;;"></td><td style="text-align: right;;">0001</td><td style=";">Week 2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">0002</td><td style=";">No</td><td style=";">Yes</td><td style=";">No</td><td style=";">Yes</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;">0002</td><td style=";">Week 5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">I2</th><td style="text-align:left">=LOOKUP(<font color="Blue">2,1/(<font color="Red">INDEX(<font color="Green">B$2:F$3,MATCH(<font color="Purple">H2,A$2:A$3,FALSE</font>),0</font>)="Yes"</font>),B$1:F$1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

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
Top