Quick lookup problem

R

Roughhouse

New Member
Joined
Oct 18, 2005
Messages
10
I have two sheets.

In sheet 1 I have a table with Column A being "Materials" and Columns C-E being "Factories", with the factory number being listed in row 1.

In Sheet 2 I have Column A being "Materials" and Column B being "Factories"

What I am trying to accomplish is have the sheet one populate itself with an "x" if the material in column A is found at the facility listed at the top of the column.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Roughhouse said:
I have two sheets.

In sheet 1 I have a table with Column A being "Materials" and Columns C-E being "Factories", with the factory number being listed in row 1.

In Sheet 2 I have Column A being "Materials" and Column B being "Factories"

What I am trying to accomplish is have the sheet one populate itself with an "x" if the material in column A is found at the facility listed at the top of the column.
Click to expand...
What version of Excel are you using?
 
Upvote 0
Roughhouse said:
I have two sheets.

In sheet 1 I have a table with Column A being "Materials" and Columns C-E being "Factories", with the factory number being listed in row 1.

In Sheet 2 I have Column A being "Materials" and Column B being "Factories"

What I am trying to accomplish is have the sheet one populate itself with an "x" if the material in column A is found at the facility listed at the top of the column.
Click to expand...

In C2 on Sheet1 enter, copy across, and down:
Code: 
=IFERROR(IF(INDEX(Sheet2!$B$2:$B$50,MATCH($A2,Sheet2!$A$2:$A$50,0))=C$1,"X",""),"")

On all versions...
Code: 
=IF(ISNUMBER(MATCH($A2,Sheet2!$A$2:$A$50,0)),
     IF(INDEX(Sheet2!$B$2:$B$50,
       MATCH($A2,Sheet2!$A$2:$A$50,0))=C$1,"X",""),
     "")
 
Upvote 0
or this as an alternative:

=IF(N(IF(ISERROR(MATCH($B5&D$4,Sheet2!$A$2:$A$11&Sheet2!$B$2:$B$11,0)),"",MATCH($B5&D$4,Sheet2!$A$2:$A$11&Sheet2!$B$2:$B$11,0))),"X","")

as array formula, shall be confirmed with Ctrl Shift Enter
 
Upvote 0
I am using Excel 2007.

I have tried the other post formulas and they don't seem to be working. I don't think I have been clear enough. My first sheet labeled "List Data"
<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 /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th><th>AI</th><th>AJ</th><th>AK</th><th>AL</th><th>AM</th><th>AN</th><th>AO</th><th>AP</th><th>AQ</th><th>AR</th><th>AS</th><th>AT</th><th>AU</th><th>AV</th><th>AW</th><th>AX</th><th>AY</th><th>AZ</th><th>BA</th><th>BB</th><th>BC</th><th>BD</th><th>BE</th><th>BF</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">SAP Code</td><td style="text-align: right;;">3100</td><td style="text-align: right;;">3200</td><td style="text-align: right;;">3400</td><td style="text-align: right;;">3600</td><td style="text-align: right;;">3700</td><td style="text-align: right;;">3900</td><td style="text-align: right;;">4600</td><td style="text-align: right;;">4800</td><td style="text-align: right;;">4825</td><td style="text-align: right;;">5200</td><td style="text-align: right;;">5300</td><td style="text-align: right;;">5500</td><td style="text-align: right;;">5501</td><td style="text-align: right;;">5600</td><td style="text-align: right;;">5800</td><td style="text-align: right;;">5900</td><td style="text-align: right;;">6100</td><td style="text-align: right;;">6300</td><td style="text-align: right;;">6400</td><td style="text-align: right;;">6700</td><td style="text-align: right;;">6800</td><td style="text-align: right;;">7000</td><td style="text-align: right;;">7100</td><td style="text-align: right;;">7300</td><td style="text-align: right;;">7500</td><td style="text-align: right;;">7510</td><td style="text-align: right;;">8000</td><td style="text-align: right;;">8100</td><td style="text-align: right;;">8800</td><td style="text-align: right;;">8900</td><td style="text-align: right;;">9000</td><td style="text-align: right;;">9100</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">AV0000</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><td style="text-align: right;;"></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><td style="text-align: right;;"></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><td style="text-align: right;;"></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><td style="text-align: right;;"></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><td style="text-align: right;;"></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><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;">3</td><td style=";">AV0001</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">AV0002</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:5.4em;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">List Data</p><br /><br />

My Second sheet is my "Pricing" sheet.
<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Material</td><td style=";">Plnt</td></tr><tr ><td style="color: #161120;text-align: center;">537</td><td style=";">AV0000</td><td style="text-align: right;;">3000</td></tr><tr ><td style="color: #161120;text-align: center;">538</td><td style=";">AV0000</td><td style="text-align: right;;">3400</td></tr><tr ><td style="color: #161120;text-align: center;">539</td><td style=";">AV0000</td><td style="text-align: right;;">4000</td></tr><tr ><td style="color: #161120;text-align: center;">540</td><td style=";">AV0000</td><td style="text-align: right;;">4800</td></tr><tr ><td style="color: #161120;text-align: center;">541</td><td style=";">AV0000</td><td style="text-align: right;;">4801</td></tr><tr ><td style="color: #161120;text-align: center;">542</td><td style=";">AV0000</td><td style="text-align: right;;">4802</td></tr><tr ><td style="color: #161120;text-align: center;">543</td><td style=";">AV0000</td><td style="text-align: right;;">4806</td></tr><tr ><td style="color: #161120;text-align: center;">544</td><td style=";">AV0000</td><td style="text-align: right;;">4820</td></tr><tr ><td style="color: #161120;text-align: center;">545</td><td style=";">AV0000</td><td style="text-align: right;;">4830</td></tr><tr ><td style="color: #161120;text-align: center;">546</td><td style=";">AV0000</td><td style="text-align: right;;">4900</td></tr><tr ><td style="color: #161120;text-align: center;">547</td><td style=";">AV0000</td><td style="text-align: right;;">5200</td></tr><tr ><td style="color: #161120;text-align: center;">548</td><td style=";">AV0000</td><td style="text-align: right;;">5201</td></tr><tr ><td style="color: #161120;text-align: center;">549</td><td style=";">AV0000</td><td style="text-align: right;;">5300</td></tr><tr ><td style="color: #161120;text-align: center;">550</td><td style=";">AV0000</td><td style="text-align: right;;">5400</td></tr><tr ><td style="color: #161120;text-align: center;">551</td><td style=";">AV0000</td><td style="text-align: right;;">5500</td></tr><tr ><td style="color: #161120;text-align: center;">552</td><td style=";">AV0000</td><td style="text-align: right;;">5501</td></tr><tr ><td style="color: #161120;text-align: center;">553</td><td style=";">AV0000</td><td style="text-align: right;;">5800</td></tr><tr ><td style="color: #161120;text-align: center;">554</td><td style=";">AV0000</td><td style="text-align: right;;">6100</td></tr><tr ><td style="color: #161120;text-align: center;">555</td><td style=";">AV0000</td><td style="text-align: right;;">6800</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">Pricing</p><br /><br />

So what I want to do is generate an X in my "List Data" sheet if it is listed next to a specific location on my "Pricing" sheet. So if my formula works correctly I would have the following results.
<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 /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th><th>AI</th><th>AJ</th><th>AK</th><th>AL</th><th>AM</th><th>AN</th><th>AO</th><th>AP</th><th>AQ</th><th>AR</th><th>AS</th><th>AT</th><th>AU</th><th>AV</th><th>AW</th><th>AX</th><th>AY</th><th>AZ</th><th>BA</th><th>BB</th><th>BC</th><th>BD</th><th>BE</th><th>BF</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">SAP Code</td><td style="text-align: right;;">3100</td><td style="text-align: right;;">3200</td><td style="text-align: right;;">3400</td><td style="text-align: right;;">3600</td><td style="text-align: right;;">3700</td><td style="text-align: right;;">3900</td><td style="text-align: right;;">4600</td><td style="text-align: right;;">4800</td><td style="text-align: right;;">4825</td><td style="text-align: right;;">5200</td><td style="text-align: right;;">5300</td><td style="text-align: right;;">5500</td><td style="text-align: right;;">5501</td><td style="text-align: right;;">5600</td><td style="text-align: right;;">5800</td><td style="text-align: right;;">5900</td><td style="text-align: right;;">6100</td><td style="text-align: right;;">6300</td><td style="text-align: right;;">6400</td><td style="text-align: right;;">6700</td><td style="text-align: right;;">6800</td><td style="text-align: right;;">7000</td><td style="text-align: right;;">7100</td><td style="text-align: right;;">7300</td><td style="text-align: right;;">7500</td><td style="text-align: right;;">7510</td><td style="text-align: right;;">8000</td><td style="text-align: right;;">8100</td><td style="text-align: right;;">8800</td><td style="text-align: right;;">8900</td><td style="text-align: right;;">9000</td><td style="text-align: right;;">9100</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">AV0000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">x</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><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style=";">x</td><td style=";">x</td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">x</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><td style="text-align: right;;"></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><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.4em;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">List Data</p><br /><br />

Thanks again for your help.
 
Upvote 0
It wasn't really necessary to put up that much data here...

List Data

AA2, just enter and copy across:

=IFERROR(IF(INDEX(Pricing!$C$537:$C$555,MATCH($D2,Pricing!$B$537:$B$555,0))=AA$1,"X",""),"")
 
Upvote 0
Sorry for too much info there, but the formula still sin't working. I am getting blanks all the way across. If I remove the IFERROR and the IF statements then all the cells populate with 3000.
 
Upvote 0
Roughhouse said:
I am using Excel 2007.

I have tried the other post formulas and they don't seem to be working. I don't think I have been clear enough. My first sheet labeled "List Data"
Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>D</TH><TH>AA</TH><TH>AB</TH><TH>AC</TH><TH>AD</TH><TH>AE</TH><TH>AF</TH><TH>AG</TH><TH>AH</TH><TH>AI</TH><TH>AJ</TH><TH>AK</TH><TH>AL</TH><TH>AM</TH><TH>AN</TH><TH>AO</TH><TH>AP</TH><TH>AQ</TH><TH>AR</TH><TH>AS</TH><TH>AT</TH><TH>AU</TH><TH>AV</TH><TH>AW</TH><TH>AX</TH><TH>AY</TH><TH>AZ</TH><TH>BA</TH><TH>BB</TH><TH>BC</TH><TH>BD</TH><TH>BE</TH><TH>BF</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">SAP Code</TD><TD style="TEXT-ALIGN: right">3100</TD><TD style="TEXT-ALIGN: right">3200</TD><TD style="TEXT-ALIGN: right">3400</TD><TD style="TEXT-ALIGN: right">3600</TD><TD style="TEXT-ALIGN: right">3700</TD><TD style="TEXT-ALIGN: right">3900</TD><TD style="TEXT-ALIGN: right">4600</TD><TD style="TEXT-ALIGN: right">4800</TD><TD style="TEXT-ALIGN: right">4825</TD><TD style="TEXT-ALIGN: right">5200</TD><TD style="TEXT-ALIGN: right">5300</TD><TD style="TEXT-ALIGN: right">5500</TD><TD style="TEXT-ALIGN: right">5501</TD><TD style="TEXT-ALIGN: right">5600</TD><TD style="TEXT-ALIGN: right">5800</TD><TD style="TEXT-ALIGN: right">5900</TD><TD style="TEXT-ALIGN: right">6100</TD><TD style="TEXT-ALIGN: right">6300</TD><TD style="TEXT-ALIGN: right">6400</TD><TD style="TEXT-ALIGN: right">6700</TD><TD style="TEXT-ALIGN: right">6800</TD><TD style="TEXT-ALIGN: right">7000</TD><TD style="TEXT-ALIGN: right">7100</TD><TD style="TEXT-ALIGN: right">7300</TD><TD style="TEXT-ALIGN: right">7500</TD><TD style="TEXT-ALIGN: right">7510</TD><TD style="TEXT-ALIGN: right">8000</TD><TD style="TEXT-ALIGN: right">8100</TD><TD style="TEXT-ALIGN: right">8800</TD><TD style="TEXT-ALIGN: right">8900</TD><TD style="TEXT-ALIGN: right">9000</TD><TD style="TEXT-ALIGN: right">9100</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>AV0000</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><TD style="TEXT-ALIGN: right"></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><TD style="TEXT-ALIGN: right"></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><TD style="TEXT-ALIGN: right"></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><TD style="TEXT-ALIGN: right"></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><TD style="TEXT-ALIGN: right"></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><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">3</TD><TD>AV0001</TD><TD style="TEXT-ALIGN: right"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>AV0002</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
List Data​




My Second sheet is my "Pricing" sheet.
Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>Material</TD><TD>Plnt</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">537</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">3000</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">538</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">3400</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">539</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">4000</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">540</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">4800</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">541</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">4801</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">542</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">4802</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">543</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">4806</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">544</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">4820</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">545</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">4830</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">546</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">4900</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">547</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">5200</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">548</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">5201</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">549</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">5300</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">550</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">5400</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">551</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">5500</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">552</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">5501</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">553</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">5800</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">554</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">6100</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">555</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right">6800</TD></TR></TBODY></TABLE>
Pricing​




So what I want to do is generate an X in my "List Data" sheet if it is listed next to a specific location on my "Pricing" sheet. So if my formula works correctly I would have the following results.
Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>D</TH><TH>AA</TH><TH>AB</TH><TH>AC</TH><TH>AD</TH><TH>AE</TH><TH>AF</TH><TH>AG</TH><TH>AH</TH><TH>AI</TH><TH>AJ</TH><TH>AK</TH><TH>AL</TH><TH>AM</TH><TH>AN</TH><TH>AO</TH><TH>AP</TH><TH>AQ</TH><TH>AR</TH><TH>AS</TH><TH>AT</TH><TH>AU</TH><TH>AV</TH><TH>AW</TH><TH>AX</TH><TH>AY</TH><TH>AZ</TH><TH>BA</TH><TH>BB</TH><TH>BC</TH><TH>BD</TH><TH>BE</TH><TH>BF</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">SAP Code</TD><TD style="TEXT-ALIGN: right">3100</TD><TD style="TEXT-ALIGN: right">3200</TD><TD style="TEXT-ALIGN: right">3400</TD><TD style="TEXT-ALIGN: right">3600</TD><TD style="TEXT-ALIGN: right">3700</TD><TD style="TEXT-ALIGN: right">3900</TD><TD style="TEXT-ALIGN: right">4600</TD><TD style="TEXT-ALIGN: right">4800</TD><TD style="TEXT-ALIGN: right">4825</TD><TD style="TEXT-ALIGN: right">5200</TD><TD style="TEXT-ALIGN: right">5300</TD><TD style="TEXT-ALIGN: right">5500</TD><TD style="TEXT-ALIGN: right">5501</TD><TD style="TEXT-ALIGN: right">5600</TD><TD style="TEXT-ALIGN: right">5800</TD><TD style="TEXT-ALIGN: right">5900</TD><TD style="TEXT-ALIGN: right">6100</TD><TD style="TEXT-ALIGN: right">6300</TD><TD style="TEXT-ALIGN: right">6400</TD><TD style="TEXT-ALIGN: right">6700</TD><TD style="TEXT-ALIGN: right">6800</TD><TD style="TEXT-ALIGN: right">7000</TD><TD style="TEXT-ALIGN: right">7100</TD><TD style="TEXT-ALIGN: right">7300</TD><TD style="TEXT-ALIGN: right">7500</TD><TD style="TEXT-ALIGN: right">7510</TD><TD style="TEXT-ALIGN: right">8000</TD><TD style="TEXT-ALIGN: right">8100</TD><TD style="TEXT-ALIGN: right">8800</TD><TD style="TEXT-ALIGN: right">8900</TD><TD style="TEXT-ALIGN: right">9000</TD><TD style="TEXT-ALIGN: right">9100</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>AV0000</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>x</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><TD>x</TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD>x</TD><TD>x</TD><TD>x</TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD style="TEXT-ALIGN: right"></TD><TD>x</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>x</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><TD style="TEXT-ALIGN: right"></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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
List Data​




Thanks again for your help.
Click to expand...
Try this...

Entered in AA2:

=IF(COUNTIFS(Pricing!$B$537:$B$555,$D2,Pricing!$C$537:$C$555,AA$1),"X","")

Copy across as needed then down as needed.
 
Upvote 0
Aladin Akyurek said:
What did you enter exactly in AA2?
Click to expand...
The reason your formula doesn't work is because it will always match the first instance of D2.

However, feel free to ignore this post just as you pretend to ignore all of my other posts! :laugh:
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
VBA to look down column and over write cell with text from list
Replies
5
Views
127
Peter_SSs
Peter_SSs
T
  • Question Question
VBA to populate cells on one worksheet with cells from another worksheet based on date help!!!
Replies
2
Views
371
Taekwon
T
W
  • Solved
Looking For Tips To Improve My VBA
Replies
8
Views
227
who am i
W
A
  • Solved
Need VBA to automatically copy paste rows based factory selected
Replies
8
Views
616
Alex Blakenburg
A
B
  • Solved
VBA for Generating Cycle Counts
2
Replies
17
Views
658
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,503
Messages
6,179,134
Members
452,890
Latest member
Nikhil Ramesh

Share this page

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
Back
Top