how can I check the value 1 cell against the values of entire row

D

Danbmarine

I would like to see if one cell is equal to any other cell in an entire row.
The row contains over 10,000 cells of data how can I insert a formula to do
this?
 
G

Gary''s Student

Use the MATCH() function. It will tell you both if its there and where it is.
 
D

Danbmarine

thanks, will this also work if the target cell is not in the same row as the
list that I am comparing it to?
 
G

Gary''s Student

yes
--
Gary''s Student


Danbmarine said:
thanks, will this also work if the target cell is not in the same row as the
list that I am comparing it to?
Click to expand...
 
Q

quimrider

I have a similar situation. I need the result to be a logical
TRUE/FALSE. I cannot figure out how to use the #N/A value in a logic
test. Basically I need to see if the reference cell value is in the
list and return TRUE if its and FALSE if it isnt. The only stumbling
block is how to deal with the #N/A error. Any suggestions?
 
J

Jay

I have a similar situation. I need the result to be a logical
TRUE/FALSE. I cannot figure out how to use the #N/A value in a logic
test. Basically I need to see if the reference cell value is in the
list and return TRUE if its and FALSE if it isnt. The only stumbling
block is how to deal with the #N/A error. Any suggestions?
Click to expand...

You can test for the #N/A error with the function ISNA; for example:
=NOT(ISNA(VLOOKUP(G1,D:D,1,FALSE)))

But if you're using VLOOKUP (or similar), you might consider using COUNTIF
instead -- there are fewer error conditions to cope with. For example:
=COUNTIF(D:D,G1)>0
 
Top