MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Finding specific data from lists


Posted by Janet on January 28, 2002 11:03 AM

I have a worksheet which contains several rows of project information. One column (Column C) is the work type code for that project and Column E displays the current dollar amount value of that project. There are many different 4 digit work codes but they all ultimately fit into 5 work type categories (ACC, PCC, Grading, Bridges & Culverts, & Misc). Elsewhere on the worksheet, I have 5 columns with each listing all the work codes for each category. I have created another 5 columns where I want to retrieve the current dollar amount for every project that has a work type code for ACC, for PCC, etc. I need a formula or method to retrieve that information. I've tried =IF(C3=(J3:J22),E3,"") which I thought would find the work type code in cell C3 and if it matched any work type code in J3 through J22, it would display the current project amount (cell D3) and if it didn't find a match, it would leave the cell blank. It sounded great - it doesn't work. Any ideas? Thanks!


Posted by Juan Pablo G. on January 28, 2002 11:35 AM

Not sure, but what about this ?

=IF(COUNTIF(J2:J23,C3),E3,"")

Juan Pablo G.

Posted by Janet on January 29, 2002 6:06 AM

THANKS!! Your formula worked great.

Janet