| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
I think this has an easy fix, but I can't seem to get my head around it right
now. I have a column of data which has a variety of formulas in it that return a variety of words. I would like a way to know if two certain words are in that column. If they are, I want to know which word is present. Here is an example Bob Mike Tom Alan I want to know if it says "John" or if it says "Tom" and it to return which name so my final result here would be "Tom". My data will never have both a John and a Tom in the same column, but Tom or John could be in row 3 or 4 or 5, etc. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup
I think this'll work
=IF(COUNTA(G1:G4)=COUNTA(SUBSTITUTE(G1:G4,"Bob","" )),"john","bob") "jenhow" wrote: I think this has an easy fix, but I can't seem to get my head around it right now. I have a column of data which has a variety of formulas in it that return a variety of words. I would like a way to know if two certain words are in that column. If they are, I want to know which word is present. Here is an example Bob Mike Tom Alan I want to know if it says "John" or if it says "Tom" and it to return which name so my final result here would be "Tom". My data will never have both a John and a Tom in the same column, but Tom or John could be in row 3 or 4 or 5, etc. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup
No - that doesn't work - should have tested it first
Try =IF(SUMPRODUCT(--(G1:G4="Bob"))0,"Bob","John") "jenhow" wrote: I think this has an easy fix, but I can't seem to get my head around it right now. I have a column of data which has a variety of formulas in it that return a variety of words. I would like a way to know if two certain words are in that column. If they are, I want to know which word is present. Here is an example Bob Mike Tom Alan I want to know if it says "John" or if it says "Tom" and it to return which name so my final result here would be "Tom". My data will never have both a John and a Tom in the same column, but Tom or John could be in row 3 or 4 or 5, etc. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup
=IF(ISERROR(VLOOKUP("Tom",A1:A20,1,0)),"John","Tom ")
or =IF(COUNTIF(A1:A20,"John"),"John","Tom") or =IF(A1:A20="John","John","Tom") "jenhow" wrote: I think this has an easy fix, but I can't seem to get my head around it right now. I have a column of data which has a variety of formulas in it that return a variety of words. I would like a way to know if two certain words are in that column. If they are, I want to know which word is present. Here is an example Bob Mike Tom Alan I want to know if it says "John" or if it says "Tom" and it to return which name so my final result here would be "Tom". My data will never have both a John and a Tom in the same column, but Tom or John could be in row 3 or 4 or 5, etc. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup
=IF(A1:A20="John","John","Tom")
Do you test the formulas that you post? Biff "Teethless mama" wrote in message ... =IF(ISERROR(VLOOKUP("Tom",A1:A20,1,0)),"John","Tom ") or =IF(COUNTIF(A1:A20,"John"),"John","Tom") or =IF(A1:A20="John","John","Tom") "jenhow" wrote: I think this has an easy fix, but I can't seem to get my head around it right now. I have a column of data which has a variety of formulas in it that return a variety of words. I would like a way to know if two certain words are in that column. If they are, I want to know which word is present. Here is an example Bob Mike Tom Alan I want to know if it says "John" or if it says "Tom" and it to return which name so my final result here would be "Tom". My data will never have both a John and a Tom in the same column, but Tom or John could be in row 3 or 4 or 5, etc. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup
Harlan?
Is that you???? <gd&r "T. Valko" wrote: =IF(A1:A20="John","John","Tom") Do you test the formulas that you post? Biff "Teethless mama" wrote in message ... =IF(ISERROR(VLOOKUP("Tom",A1:A20,1,0)),"John","Tom ") or =IF(COUNTIF(A1:A20,"John"),"John","Tom") or =IF(A1:A20="John","John","Tom") "jenhow" wrote: I think this has an easy fix, but I can't seem to get my head around it right now. I have a column of data which has a variety of formulas in it that return a variety of words. I would like a way to know if two certain words are in that column. If they are, I want to know which word is present. Here is an example Bob Mike Tom Alan I want to know if it says "John" or if it says "Tom" and it to return which name so my final result here would be "Tom". My data will never have both a John and a Tom in the same column, but Tom or John could be in row 3 or 4 or 5, etc. -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup
<gd&r
I had to Google that one! Biff "Dave Peterson" wrote in message ... Harlan? Is that you???? <gd&r "T. Valko" wrote: =IF(A1:A20="John","John","Tom") Do you test the formulas that you post? Biff "Teethless mama" wrote in message ... =IF(ISERROR(VLOOKUP("Tom",A1:A20,1,0)),"John","Tom ") or =IF(COUNTIF(A1:A20,"John"),"John","Tom") or =IF(A1:A20="John","John","Tom") "jenhow" wrote: I think this has an easy fix, but I can't seem to get my head around it right now. I have a column of data which has a variety of formulas in it that return a variety of words. I would like a way to know if two certain words are in that column. If they are, I want to know which word is present. Here is an example Bob Mike Tom Alan I want to know if it says "John" or if it says "Tom" and it to return which name so my final result here would be "Tom". My data will never have both a John and a Tom in the same column, but Tom or John could be in row 3 or 4 or 5, etc. -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
lookup
That works great once the data is filled in, but can I make it so that if no
data is filled it, it won't give me "John" as an answer? So I would like nothing returned if neither john or bob is present, then once the column has data it will find john or bob. Does that make sense? Thanks for you help. "Duke Carey" wrote: No - that doesn't work - should have tested it first Try =IF(SUMPRODUCT(--(G1:G4="Bob"))0,"Bob","John") "jenhow" wrote: I think this has an easy fix, but I can't seem to get my head around it right now. I have a column of data which has a variety of formulas in it that return a variety of words. I would like a way to know if two certain words are in that column. If they are, I want to know which word is present. Here is an example Bob Mike Tom Alan I want to know if it says "John" or if it says "Tom" and it to return which name so my final result here would be "Tom". My data will never have both a John and a Tom in the same column, but Tom or John could be in row 3 or 4 or 5, etc. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
| How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Linear Mode
