Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |