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

[Pavan Kumar]

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-comfficeffice" /><o></o>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></o>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>I am using Office 2007</o>
<o>Please advice</o>
Regards,
Pavan

 

[Comfy]

<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]

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]

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

 

[ansh200431]

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)
200000	80	8
500000	10	5
500000	20	6
500000	30	7
500000	40	8
500000	50	9
500000	60	10
500000	70	11
500000	80	12

<tbody>
</tbody>

SHEET 2 above....The comparision continues with 600000, 700000, etc.

Round Up Profit Percent(U)	Actual revenue	Round up Actual Value(W)		% needs to be shared with the institution(Y)
80.00%	1,78,000	2,00,000		NO MATCH
500.00%	5,98,000	6,00,000		NO MATCH
430.00%	5,25,000	5,00,000		NO MATCH
530.00%	6,25,000	6,00,000		NO MATCH
400.00%	5,00,000	5,00,000		NO 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]

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")

 

[ansh200431]

Thank You very much confy..It is solved. Thanks again