vlookup when value/text is not in left-most column

[farmerdave]

Hi, i have spent a day thinking about this, haven't found a solution.

I have a list of possible text values (cells B5:B223) and want to find if any of these values occur in a table_array (cells C5:H223) which has plenty of empty cells. My understanding is that vlookup will search the left-most column (C), but what if some values don't appear in column C but appear for the first time in column D or H?

My goal is to count the number of entries from column B that have at least one occurrence in the table_array.

This is what i have so far:

=IF(ISERROR(VLOOKUP(B5,$C$5:$H$223,1,FALSE))," ",VLOOKUP(B5,$C$5:$H$223,1,FALSE))

Any help greatly welcomed.

[Ron Coderre]

In your description, you mentioned that you want to COUNT the number of
times the Col_B item occurs in the Col_C through Col_H range.

if that's true...this column counts the occurrences of B5's contents
in C5:H223

Please Login or Register  to view this content.

If you want to know if the B5 value occurs 1 or more times in that range

Please Login or Register  to view this content.

Either way...Copy the formula down as far as you need.

Is that something you can work with?

[farmerdave]

It sure is! I was just reading about duplicates and had modified the formula suggested:

=IF(ISBLANK(B5),"",IF(COUNTIF($B$5:$H$223,B5)=1,"",1))

which gives me everything i need.

Thanks.

[farmerdave]

Another way i found, which will also display which entries appear in the table is to enter in column J

{=IF(OR(B5=$C$5:$H$223),B5,0)}

and copy down, and then

{=SUM(ISTEXT(J5:J223)*1)}

will give the number of entries.