If and Lookup
 

I want to return cell value A when cell value B is found in a range of cells
in a separate one column of values. If cell value A is not found in the
range I want a red capital "X" returned. I think this would involve an IF
function combined with some type of LOOKUP, but I'm not sure. Can someone
help?
--
Bruce Kollister

If and Lookup
 

One guess, maybe something like this

Assuming col B to be tested vs values in col H
In C1: =IF(A1="","",IF(COUNTIF(H:H,B1),A1,"X"))
Copy down

Then to get the X's in red font
Select col C, apply CF using Formula Is:
=C1="X"
Format the font as red, ok out

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"bruster" wrote:
I want to return cell value A when cell value B is found in a range of cells
in a separate one column of values. If cell value A is not found in the
range I want a red capital "X" returned. I think this would involve an IF
function combined with some type of LOOKUP, but I'm not sure. Can someone
help?
--
Bruce Kollister

If and Lookup
 

Try the below. Sheet2A:A contains the other table

Col A Col B Col C
1 One =IF(ISNA(MATCH(B2,Sheet2!A:A,0)),"",A2)
2 Two
3 Three
4 Four
5 Five

If this post helps click Yes
---------------
Jacob Skaria


"bruster" wrote:

I want to return cell value A when cell value B is found in a range of cells
in a separate one column of values. If cell value A is not found in the
range I want a red capital "X" returned. I think this would involve an IF
function combined with some type of LOOKUP, but I'm not sure. Can someone
help?
--
Bruce Kollister

If and Lookup
 

Oops.. I missed the second part; and Conditional Formatting as mentioned by
Max..

=IF(ISNA(MATCH(B2,Sheet2!A:A,0)),"X",A2)


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below. Sheet2A:A contains the other table

Col A Col B Col C
1 One =IF(ISNA(MATCH(B2,Sheet2!A:A,0)),"",A2)
2 Two
3 Three
4 Four
5 Five

If this post helps click Yes
---------------
Jacob Skaria


"bruster" wrote:

I want to return cell value A when cell value B is found in a range of cells
in a separate one column of values. If cell value A is not found in the
range I want a red capital "X" returned. I think this would involve an IF
function combined with some type of LOOKUP, but I'm not sure. Can someone
help?
--
Bruce Kollister

If and Lookup
 

Your answer seems to test one column against another. Let me try my question
again. I want to test value in H4 to see if it matches any of the values in
Col B4...B19. If so, I want to return value of C4. If not, I want the red
"X".
--
Bruce Kollister


"Max" wrote:

One guess, maybe something like this

Assuming col B to be tested vs values in col H
In C1: =IF(A1="","",IF(COUNTIF(H:H,B1),A1,"X"))
Copy down

Then to get the X's in red font
Select col C, apply CF using Formula Is:
=C1="X"
Format the font as red, ok out

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"bruster" wrote:
I want to return cell value A when cell value B is found in a range of cells
in a separate one column of values. If cell value A is not found in the
range I want a red capital "X" returned. I think this would involve an IF
function combined with some type of LOOKUP, but I'm not sure. Can someone
help?
--
Bruce Kollister

If and Lookup
 

=IF(ISNA(MATCH(H4,B4:B19,0)),"X",C4)

and conditional format the formula column.

If this post helps click Yes
---------------
Jacob Skaria


"bruster" wrote:

Your answer seems to test one column against another. Let me try my question
again. I want to test value in H4 to see if it matches any of the values in
Col B4...B19. If so, I want to return value of C4. If not, I want the red
"X".
--
Bruce Kollister


"Max" wrote:

One guess, maybe something like this

Assuming col B to be tested vs values in col H
In C1: =IF(A1="","",IF(COUNTIF(H:H,B1),A1,"X"))
Copy down

Then to get the X's in red font
Select col C, apply CF using Formula Is:
=C1="X"
Format the font as red, ok out

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"bruster" wrote:
I want to return cell value A when cell value B is found in a range of cells
in a separate one column of values. If cell value A is not found in the
range I want a red capital "X" returned. I think this would involve an IF
function combined with some type of LOOKUP, but I'm not sure. Can someone
help?
--
Bruce Kollister

If and Lookup
 

Outstanding, Jacob. Your first reply worked perfectly. I'm sorry I didn't
realize it right away. Can I possibly get your email address for future
reference. You are good. Thank you very much.
--
Bruce Kollister


"Jacob Skaria" wrote:

=IF(ISNA(MATCH(H4,B4:B19,0)),"X",C4)

and conditional format the formula column.

If this post helps click Yes
---------------
Jacob Skaria


"bruster" wrote:

Your answer seems to test one column against another. Let me try my question
again. I want to test value in H4 to see if it matches any of the values in
Col B4...B19. If so, I want to return value of C4. If not, I want the red
"X".
--
Bruce Kollister


"Max" wrote:

One guess, maybe something like this

Assuming col B to be tested vs values in col H
In C1: =IF(A1="","",IF(COUNTIF(H:H,B1),A1,"X"))
Copy down

Then to get the X's in red font
Select col C, apply CF using Formula Is:
=C1="X"
Format the font as red, ok out

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"bruster" wrote:
I want to return cell value A when cell value B is found in a range of cells
in a separate one column of values. If cell value A is not found in the
range I want a red capital "X" returned. I think this would involve an IF
function combined with some type of LOOKUP, but I'm not sure. Can someone
help?
--
Bruce Kollister