lookup based on 2 values

[k_babb]

i was wondering if it is possible to so a vlookup based on two values

so column B has a transaction date column D has the Value i need to be able to look up the invoice number on a separate sheet by matching the date and the value and looking them up on sheet2


Also can someone please tell me how to word an if statement so that i can enter two values something like this =IF (A1 = "Pj" or "ps",1,0)
thanks

 

[Sal Paradise]

If statement is easy:
=IF(OR(A1="Pj",A1="ps"),1,0)

Could you give us a better explanation of what you're trying to do with the vlookup based on two values?

You want to look up an item located XXXX by matching condition AAAAA with the data in BBBBB and condition CCCCC with the data in DDDDD.

Something like that.

 

[adam087]

Second first:
=IF(OR(A1="PJ", A1="PS"), 1, 0)

First one I'm a little vague on what you're asking. I find the easiest way to lookup based on values in two columns (if that's what you want) is to combine them into another 'helper' column (e.g. new column A: A2=B2&C2). This then creates a sort of Primary Key which should be distinct, to do lookups on.

Regards
Adam

 

[markmzz]

Maybe this (in C2 is a array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">Transaction Date</td><td style="text-align: center;background-color: #C5D9F1;;">Value</td><td style="text-align: center;background-color: #C5D9F1;;">Invoice #</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">If statement</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1/1/2011</td><td style="text-align: center;;">5100</td><td style="text-align: center;;">50100</td><td style="text-align: right;;"></td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></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="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet21</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: #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">E2</th><td style="text-align:left">=1*OR(<font color="Blue">A1="Pj",A1="ps"</font>)</td></tr></tbody></table></td></tr></table><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>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">C2</th><td style="text-align:left">{=INDEX(<font color="Blue">Sheet2!A2:A10,MATCH(<font color="Red">A2&B2,Sheet2!$B$2:$B$10&Sheet2!$D$2:$D$10,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 />
Markmzz

 

[k_babb]

basically i have a sheet with invoices on them sheet 2 which has date value and invoice number

on sheet 1 I have just the date and value and want to lookup the invoice number from sheet 2
so i have sorted sheet 2 by date so now i need to search for the date on sheet 2 and then once it finds that date look up the value for that date and then once it has a match return the invoice number

 

[markmzz]

basically i have a sheet with invoices on them sheet 2 which has date value and invoice number

on sheet 1 I have just the date and value and want to lookup the invoice number from sheet 2
so i have sorted sheet 2 by date so now i need to search for the date on sheet 2 and then once it finds that date look up the value for that date and then once it has a match return the invoice number

Look at this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Invoice #</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Transaction Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Col03</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">Value</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">50100</TD><TD style="TEXT-ALIGN: center">1/1/2011</TD><TD style="TEXT-ALIGN: center">Data01</TD><TD style="TEXT-ALIGN: center">5100</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">55600</TD><TD style="TEXT-ALIGN: center">1/1/2011</TD><TD style="TEXT-ALIGN: center">Data02</TD><TD style="TEXT-ALIGN: center">7400</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">45800</TD><TD style="TEXT-ALIGN: center">2/8/2011</TD><TD style="TEXT-ALIGN: center">Data03</TD><TD style="TEXT-ALIGN: center">8400</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">64200</TD><TD style="TEXT-ALIGN: center">2/8/2011</TD><TD style="TEXT-ALIGN: center">Data04</TD><TD style="TEXT-ALIGN: center">5300</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">85900</TD><TD style="TEXT-ALIGN: center">3/18/2011</TD><TD style="TEXT-ALIGN: center">Data05</TD><TD style="TEXT-ALIGN: center">7800</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">44700</TD><TD style="TEXT-ALIGN: center">3/18/2011</TD><TD style="TEXT-ALIGN: center">Data06</TD><TD style="TEXT-ALIGN: center">5900</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">10600</TD><TD style="TEXT-ALIGN: center">3/18/2011</TD><TD style="TEXT-ALIGN: center">Data07</TD><TD style="TEXT-ALIGN: center">4900</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">32500</TD><TD style="TEXT-ALIGN: center">5/14/2011</TD><TD style="TEXT-ALIGN: center">Data08</TD><TD style="TEXT-ALIGN: center">6200</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">31800</TD><TD style="TEXT-ALIGN: center">5/14/2011</TD><TD style="TEXT-ALIGN: center">Data09</TD><TD style="TEXT-ALIGN: center">4100</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</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="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD><TD style="TEXT-ALIGN: center">*******</TD></TR></TBODY></TABLE>Sheet2


<b>Excel 2007</b><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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">Transaction Date</td><td style="text-align: center;background-color: #C5D9F1;;">Value</td><td style="text-align: center;background-color: #C5D9F1;;">Invoice #</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #C5D9F1;;">If statement</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">3/18/2011</td><td style="text-align: center;;">5900</td><td style="text-align: center;;">44700</td><td style="text-align: right;;"></td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></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="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</td><td style="text-align: center;;">*******</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;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: #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">E2</th><td style="text-align:left">=1*OR(<font color="Blue">A1="Pj",A1="ps"</font>)</td></tr></tbody></table></td></tr></table><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>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">C2</th><td style="text-align:left">{=INDEX(<font color="Blue">Sheet2!$A$2:$A$10,MATCH(<font color="Red">A2&B2,Sheet2!$B$2:$B$10&Sheet2!$D$2:$D$10,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 />
Markmzz

 

[k_babb]

thank you that works great

 

[markmzz]

thank you that works great

Thanks for the feedback.

Markmzz