Find if contents of Cell is in 1 of 3 lists and label accordingly

Hi folks,

I'm a newbie to the forum and a basic user of excel, I tend to look up what I need as I go along. I hope someone can help.

We have a database that gives out info that has an identifier as to where something belongs, eg things that belong in the west area have W1, W2 or W3 associated with them. I have simplified as the actual data is long!

I need a formula that will look at three columns of data (I have called them West North & South) and report which list the identifier appears in. So in my example attached for C2 I want it to check columns E, F & G and find the answer West.

I hope I've made it clear.

Many TIA,
Dan.

Put this formula in C2:

=INDEX(E$1:G$1,IF(COUNTIF(E:E,B2),1,IF(COUNTIF(F:F,B2),2,IF(COUNTIF(G:G,B2),3,0))))

then copy down.

Hope this helps.

Pete

Hi danbak

Another version which maybe of some help in C2 and copy down:

Formula:

Please Login or Register  to view this content.

Room for another one? Put this in C2:

=INDEX(E$1:G$1,MATCH(LEFT(B2)&"*",E$1:G$1,0))

and copy down.

Pete

Thanks to all for such a speedy response, fabulous.

I think I get the first two responses.

@PeteUK, I think I get what's going on in your second response but could you help me learn and explain how the LEFT(B2)&"*" works.

@FDibbins, I created the ranges and if I put your formula in C2 it returns "N3", which is what is in cell B3. Could you explain it out for me.

Thanks again folks.

Well, LEFT(B2) without a second parameter will just take the first letter of B2, and you can use wildcards with MATCH, so basically the second part of the formula finds the column where the first letter matches with the first letter of B2 - INDEX returns the contents of the cell in E1:G1 whose column is given by the MATCH function.

Hope this helps.

Pete

Another one.....

=LOOKUP(9^9,SEARCH(LEFT(B2),E$1:G$1),E$1:G$1)

Thanks folks.

I really did simplify the data, the real stuff isn't as helpful as starting with the letter that corresponds to the area, it can start with anything. Unfortunately those ones won't work. I have created 3 data ranges so I can keep them on a separate sheet and modified PeteUK's slightly to

=INDEX(Data!A$1:Data!C$1,IF(COUNTIF(North,B2),1,IF(COUNTIF(South,B2),2,IF(COUNTIF(West,B2),3,0))))

Which works great but some don't appear on any of the three lists as they are out of our areas and I get the #Value!, can I add something such as an ISNA so that I can relace the #Value! with "Out of Area" or some other text.

The help is appreciated,
Dan.

Well, it's a bit clunky, but you could do this:

=IF(ISNA(INDEX(Data!A$1:Data!C$1,IF(COUNTIF(North,B2),1,IF(COUNTIF(South,B2),2,IF(COUNTIF(West,B2),3,0))))),"Out of Area",INDEX(Data!A$1:Data!C$1,IF(COUNTIF(North,B2),1,IF(COUNTIF(South,B2),2,IF(COUNTIF(West,B2),3,0)))))

Slightly shorter would be:

=IF(COUNTIF(range,B2)=0,"Out of Area",INDEX(Data!A$1:Data!C$1,IF(COUNTIF(North,B2),1,IF(COUNTIF(South,B2),2,IF(COUNTIF(West,B2),3,0)))))

where range will refer to all 3 columns of your data in that other sheet (e.g. 'Sheet 2'!A:C, but I'm guessing as I don't know what you've called that sheet or which columns you have used).

Hope this helps.

Pete