IF Statement involving a List

[ngor3431]

Hi,

Im trying to allocate a role title to report for staff members from data that is exported from our companies reporting system. I originally did it like this:
=IF(OR(B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x",B2="x"),"Role Title 1",IF(OR(B2="x",B2="x",B2="x",B2="x",B2="x"),"Role Title 2",IF(OR(B2="x",B2="x",B2="x",B2="x"),"Role Title 3",IF(OR(B2="x",B2="x",B2="x"),"Role Title 4","External Source")))

Where x is the name of the companies staff members and there corresponding role title, external source obviously is the false value.

This formula works fine for the purpose, it took me about an hour to enter (I had to look up everyones role titles etc), however if the staff change roles or new staff i would prefer not to have to edit the formula too much. So i tried entering the names into a different sheet and creating the formula from the list. The formula I used:

=IF(B2=Sheet2!A3:A30,"Role Title 1"), hasnt worked (I obviously haven't got to the other role titles as my first preliminary effort failed.

I get the #VALUE1 error, I have ensure each lot of cells are TEXT format.

Does anyone have any ideas? Is there a better/different formula that would work?

Cheers.

[canapone]

Hi,

a possibility

Please Login or Register  to view this content.

Hope it helps

[JBeaucaire]

I would recommend you look at creating a standard lookup table on sheet2 listing all the names in column A, then corresponding values for each in column B, the use a simple VLOOKUP() against the value you enter into sheet1 B2 against that table.

=VLOOKUP(B2, Sheet2!$A:$B, 2, 0)

or...

=IFERROR(VLOOKUP(B2, Sheet2!$A:$B, 2, 0), "External Source")

[ngor3431]

Thanks Very Much, Both worked, Vlookup a little less complicated!