Two columns(A&B) in Sheet1 & Sheet 2 must match and lookup Column C

Pavan Kumar

New Member
Joined
Nov 17, 2011
Messages
1
Hi Experts,
From last two days I am struggling to use Vlookup I know to use vlookup based on one column but my requirement is.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>IF State code (column A) & State Name (Column B) matches in Sheet1 and Sheet2
Then Unique ID (Column C) in Sheet 1 must lookup and execute data in Sheet2 (column C)
<o:p></o:p>I can't lookup based up on one column i.e., State code is repetitive and State name is also repetitive they are not unique but with the combination of state code and state name they will be unique.
<o:p>I am using Office 2007</o:p>
<o:p>Please advice</o:p>
Regards,
Pavan
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">123</td><td style=";">ABC</td><td style=";">123ABC Match</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ABC</td><td style="text-align: right;;">123</td><td style=";">ABC123 Match</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">987</td><td style="text-align: right;;">987</td><td style=";">No Match Found</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">789</td><td style="text-align: right;;">789</td><td style=";">No Match Found</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>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">C1</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet2!$C$1:$C$4,MATCH(<font color="Green">1,(<font color="Purple">Sheet2!$A$1:$A$4=Sheet1!A1</font>)*(<font color="Purple">Sheet2!$B$1:$B$4=Sheet1!B1</font>),0</font>)</font>),"No Match Found"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet2!$C$1:$C$4,MATCH(<font color="Green">1,(<font color="Purple">Sheet2!$A$1:$A$4=Sheet1!A2</font>)*(<font color="Purple">Sheet2!$B$1:$B$4=Sheet1!B2</font>),0</font>)</font>),"No Match Found"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet2!$C$1:$C$4,MATCH(<font color="Green">1,(<font color="Purple">Sheet2!$A$1:$A$4=Sheet1!A3</font>)*(<font color="Purple">Sheet2!$B$1:$B$4=Sheet1!B3</font>),0</font>)</font>),"No Match Found"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet2!$C$1:$C$4,MATCH(<font color="Green">1,(<font color="Purple">Sheet2!$A$1:$A$4=Sheet1!A4</font>)*(<font color="Purple">Sheet2!$B$1:$B$4=Sheet1!B4</font>),0</font>)</font>),"No Match Found"</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 />

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">123</td><td style=";">ABC</td><td style=";">123ABC Match</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ABC</td><td style="text-align: right;;">123</td><td style=";">ABC123 Match</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">ABC</td><td style="text-align: right;;">987</td><td style=";">ABC987 Match</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">123</td><td style="text-align: right;;">789</td><td style=";">123789 Match</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">Sheet2</p><br /><br />
 

ansh200431

New Member
Joined
Feb 13, 2013
Messages
6
I am also struggling with the above problem. I need a quick solution plz. The above formula is returning "No Match Found" even if the answer is matching.
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
It would be helpful if you could post some additional info like your version of excel and some sample data (an example)
 

ansh200431

New Member
Joined
Feb 13, 2013
Messages
6

ADVERTISEMENT

It would be helpful if you could post some additional info like your version of excel and some sample data (an example)

Dear Comfy,

Find the details below. I am using excel 2010
Actual Revenue(A)Profit % (B)% needs to be shared (C)
200000808
500000105
500000206
500000307
500000408
500000509
5000006010
5000007011
5000008012

<tbody>
</tbody>


SHEET 2 above....The comparision continues with 600000, 700000, etc.
Round Up Profit Percent(U)Actual revenueRound up Actual Value(W)% needs to be shared with the institution(Y)
80.00%1,78,0002,00,000NO MATCH
500.00%5,98,0006,00,000NO MATCH
430.00%5,25,0005,00,000NO MATCH
530.00%6,25,0006,00,000NO MATCH
400.00%5,00,0005,00,000NO MATCH

<tbody>
</tbody>


Sheet 1 Above.

I need to compare Round Up Actual Value and Round up actual Percentage in Sheet 1 with Actual Value and Profit % in Sheet 2 and Populate Sheet 1 (In Place of NO MATCH) with appropriate "% needs to be shared" from Sheet 2.

I have used

The below formula =IFERROR(INDEX(Sheet2!C4:C73,MATCH(1,(Sheet1!W11=Sheet2!A4:A73)*(Sheet1!U11=Sheet2!B4:B73),0)),"NO MATCH")

Still it's not working.

For your convenience, I have mentioned column Alphabets in the brackets.

Need a quick solution. Urgent Please

Anshuman Rath
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
Forgive me as I'm not familiar with different language settings in excel but your Sheet 1 columns has some strange formatting (an extra column)

Also I suspect if that isn't the problem then it could the difference between your "
Round Up Profit Percent(U)" being an actual percentage and "Profit % (B)" being a whole number.

Could you change the formatting of both of those columns to general and repost the data. If I'm correct Profit % will stay the same and the other column will change to 0.8, 5, 4.3 etc etc

Edit:

I've also spotted that you have the match criteria the wrong way round.

(Sheet1!W11=Sheet2!A4:A73)*(Sheet1!U11=Sheet2!B4:B73) should read

(Sheet2!A4:A73=Sheet1!W11)*(Sheet2!B4:B73=Sheet1!U11)

and finally you should make the index and search ranges absolute:

=IFERROR(INDEX(Sheet2!$C$4:$C$73,MATCH(1,(Sheet2!$A$4:$A$73=Sheet1!W11)*(Sheet2!$B$4:$B$73=Sheet1!U11),0)),"NO MATCH")
 
Last edited:

Forum statistics

Threads
1,147,482
Messages
5,741,409
Members
423,658
Latest member
Kumaradas

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