Refewrence tables

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

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.
 
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))))
 
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.
 
Back
Top