Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Refewrence tables
I have a set of values in column A, and 4 reference columns C, D, and E. I
want to reference column A to find a matching value in either column C,D or E and return a value of the column the match was found in. For example in column A row 2 the value is "jackson" I want excell to look in column C, D and E and if "jackson" is found in column D, then return the value D. The formula will be located in column B. Is this possible? I am stumped. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Refewrence tables
So the value in column D contains Jackson, but could contain other text, too?
Like: I had to give the bounce a couple of Jackson's to get into the bar. Then I'd use something like: =IF(A1="","",IF(COUNTIF(C1:E1,"*"&A1&"*")=0,"not there", INDEX(C1:E1,MATCH("*"&A1&"*",C1:E1,0)))) Jim wrote: I have a set of values in column A, and 4 reference columns C, D, and E. I want to reference column A to find a matching value in either column C,D or E and return a value of the column the match was found in. For example in column A row 2 the value is "jackson" I want excell to look in column C, D and E and if "jackson" is found in column D, then return the value D. The formula will be located in column B. Is this possible? I am stumped. -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Refewrence tables
Dave, thanks, what I ended up doing was taking the values of Column C, D and
E and placing then in one column and inputting a common number in the next column like this A B C D jackson Jones 1 Johnson Jackson 2 Jones Joseph 3 Joseph Johnson 4 Jackson Jackson Jones Jackson and in column B i entered the formula =VLOOKUP(M2,$Z$2:$AA$157,2) What I needed to know was how many transactions were done by each user and what shift they were on. Thanks again for your help. "Dave Peterson" wrote: So the value in column D contains Jackson, but could contain other text, too? Like: I had to give the bounce a couple of Jackson's to get into the bar. Then I'd use something like: =IF(A1="","",IF(COUNTIF(C1:E1,"*"&A1&"*")=0,"not there", INDEX(C1:E1,MATCH("*"&A1&"*",C1:E1,0)))) Jim wrote: I have a set of values in column A, and 4 reference columns C, D, and E. I want to reference column A to find a matching value in either column C,D or E and return a value of the column the match was found in. For example in column A row 2 the value is "jackson" I want excell to look in column C, D and E and if "jackson" is found in column D, then return the value D. The formula will be located in column B. Is this possible? I am stumped. -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenate tables into html tables | Excel Discussion (Misc queries) | |||
concatenate tables into html tables | New Users to Excel | |||
Antil Log Tables from Log Tables | New Users to Excel | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Look up tables | Excel Discussion (Misc queries) |