lookup based on two columns and return result in third column of the same row

[twildone]

I would like to be able to retrieve the result from a third column (C) that is based on the results in two other columns (A) and (B). If the results in each of column A and column B for the same row is satisfied, then the corresponding result in column C is reported. I was thinking this might be done with LOOKUP. Any help would be greatly appreciated.

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Type A</td><td style=";">Red</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Criteria 1</td><td style="font-weight: bold;text-align: center;;">Criteria 2</td><td style="font-weight: bold;text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Type A</td><td style=";">Blue</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;">Type B</td><td style="text-align: center;;">Blue</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Type A</td><td style=";">Green</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Type A</td><td style=";">Orange</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Type A</td><td style=";">Yellow</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Type B</td><td style=";">Red</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #FFFF99;;">Type B</td><td style="background-color: #FFFF99;;">Blue</td><td style="text-align: right;background-color: #FFFF99;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Type B</td><td style=";">Green</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Type B</td><td style=";">Orange</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Type B</td><td style=";">Yellow</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><br /><br /><table 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>Array 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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">G2</th><td style="text-align:left">{=INDEX(<font color="Blue">C1:C10,MATCH(<font color="Red">E2,IF(<font color="Green">B1:B10=F2,A1:A10</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[eternity1]

hi everybody,

I am having a similar problem, therefore I am not opening a new thread.

@Alphafrog:
In column B I have different dates instead of the strings. I am trying to match these data. Now sometimes there is not the same date available, is it possible to make Excel then giving the value of the closest date to the non-available?

i.e. its the 2005-12-31 as criteria, but in the list to find, there is only 2005-12-29 (+/-1, +/-2 for example).
so instead of #N/A Excel should pull the value of 2005-12-29?

Thank you

 

[AlphaFrog]

I'm not sure of the best construct for +/- dates. It's probably better if you start a new thread. There are several people here that are much better at formulas than I. They will be more likely to respond to a new thread than a three-year (coincidentally to the day) old one.

 

[eternity1]

thank you for your response. I will open a new thread