Quick Formula Question (optimism)

  • Thread starter Thread starter Joey Leake
  • Start date Start date
J

Joey Leake

I have 2 columns, and I want to search through column A for all instances of
a specific value, then if that value is found, check the corresponding
column B. If any of the cells checked in column B is a specific value, I
want the whole thing to just return TRUE. Otherwise, FALSE. I've tried
VLOOKUP and COUNTIF, but I can't seem to come up with a winning combination
here. I'm pretty sure this can be done, without macros, but I can't seem to
crack it. Any help would be greatly appreciated.
 
Joey said:
I have 2 columns, and I want to search through column A for all
instances of a specific value, then if that value is found, check the
corresponding column B. If any of the cells checked in column B is a
specific value, I want the whole thing to just return TRUE.
Otherwise, FALSE. I've tried VLOOKUP and COUNTIF, but I can't seem to
come up with a winning combination here. I'm pretty sure this can be
done, without macros, but I can't seem to crack it. Any help would be
greatly appreciated.
Click to expand...

Hi Joey
try
=IF(SUMPRODUCT((A1:A1000=test_value)*(B1:B1000=test_value2))>0,TRUE,FAL
SE)

HTH
Frank
 
Very much like the question from mp, very near to yours, so I just copied
the answer given there:

Easiest is to insert an extra column, which concatenates the first two,
preferrably with a separator, to avoid "Jan East" and "Jane Ast" to be the
same.
Something like =A1&"*"&B1
Of course your search argument needs the same treatment.
Make sure you have your data sorted the right way, and have the forth
argument of Vlookup be FALSE.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Back
Top