**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula that will tell me if a cell contains a value from a list. I don't want to do a lookup of a value in a list.

Solved by A. J. in 33 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
16/03/2018 - 07:31

hi

Excelchat Expert
16/03/2018 - 07:31

welcome to got it pro

Excelchat Expert
16/03/2018 - 07:31

how may i help you

User
16/03/2018 - 07:31

Thanks.

User
16/03/2018 - 07:32

I need a formula that tests multiple values against the value of a cell

User
16/03/2018 - 07:32

sort of like lookup("a,b,c,"A1)

Excelchat Expert
16/03/2018 - 07:32

can you help with some sample data so that i can help

User
16/03/2018 - 07:33

I want to know if cell A1 contains a value from my list.

User
16/03/2018 - 07:34

It is the reverse of what you are doing.

Excelchat Expert
16/03/2018 - 07:34

yes

Excelchat Expert
16/03/2018 - 07:34

column A is list

User
16/03/2018 - 07:35

In your example, I want to know if the values from column A exist in cell C1

User
16/03/2018 - 07:37

I know how to do a lookup from one cell to a list, I need the reverse.

Excelchat Expert
16/03/2018 - 07:38

ok

Excelchat Expert
16/03/2018 - 07:38

you want to know if A exists in list in C?

User
16/03/2018 - 07:39

No, I want to know if A1:A9 exists in C1

Excelchat Expert
16/03/2018 - 07:39

see column B

Excelchat Expert
16/03/2018 - 07:39

you can use iferror on top of vlookup

Excelchat Expert
16/03/2018 - 07:39

does this help ?

Excelchat Expert
16/03/2018 - 07:40

this will tell you if any value in column A is not there in list in C

User
16/03/2018 - 07:40

No. I need to see if any of the values in A1:A9 exist in C1

User
16/03/2018 - 07:40

It is a nested formula

Excelchat Expert
16/03/2018 - 07:40

so A,B,C and D exist

User
16/03/2018 - 07:40

YEs

Excelchat Expert
16/03/2018 - 07:41

but then there could be multiple values that could exist in C1

User
16/03/2018 - 07:41

C1 is just a value

User
16/03/2018 - 07:41

In your example is not multiple values

Excelchat Expert
16/03/2018 - 07:42

yes

User
16/03/2018 - 07:42

A through I either exist in C1 or not

Excelchat Expert
16/03/2018 - 07:42

you mean whole list?

User
16/03/2018 - 07:42

Any value in the list

Excelchat Expert
16/03/2018 - 07:43

in my example we can see that out of A1:A9 A1 to A4 exist

Excelchat Expert
16/03/2018 - 07:43

in list in column C

User
16/03/2018 - 07:43

not list, single cell

Excelchat Expert
16/03/2018 - 07:45

please tell more about requirement

Excelchat Expert
16/03/2018 - 07:45

can you create some sample data

Excelchat Expert
16/03/2018 - 07:45

and let me know how you want the output

User
16/03/2018 - 07:45

IFS(AND([Type]="Resource",ISNUMBER(SEARCH("OSL-EUGENE",[Item Number]))),"LABOR OUTSIDE",AND([Type]="Item",ISNUMBER(SEARCH("RBLT",[Cross-Reference-No]))),"REBUILT"

User
16/03/2018 - 07:45

This is part of the formula I wrote already.

Excelchat Expert
16/03/2018 - 07:46

can you create a sample in worksheet so that i can help you in better way

User
16/03/2018 - 07:46

AND([Type]="G/L Account",OR([Item Number]="41003",[Item Number]="51003")),"EQUIPMENT FREIGHT"

User
16/03/2018 - 07:46

more formula

User
16/03/2018 - 07:47

This is the complete formula that I am trying to make more efficient.

User
16/03/2018 - 07:47

=IFS(AND([Type]="Resource",ISNUMBER(SEARCH("OSL-EUGENE",[Item Number]))),"LABOR OUTSIDE",AND([Type]="Item",ISNUMBER(SEARCH("RBLT",[Cross-Reference-No]))),"REBUILT",AND([Type]="Item",ISNUMBER(SEARCH("RBLT",[Description]))),"REBUILT",AND([Type]="Item",ISNUMBER(SEARCH("REBUILT",[Description]))),"REBUILT",AND([Type]="Item",ISNUMBER(SEARCH("REPAIR",[Description]))),"REPAIR",AND([Type]="Item",ISNUMBER(SEARCH("CORE",[Description]))),"CORE",[Type]="Item","PARTS",[Amount]=0,"BLANKS",AND([Type]="G/L Account",OR([Item Number]="41002",[Item Number]="51002",TRIM([Item Number])="60440")),"PARTS NON-STOCK",AND([Type]="G/L Account",OR([Item Number]="41003",[Item Number]="51003")),"EQUIPMENT FREIGHT",AND([Type]="G/L Account",OR([Item Number]="41005",[Item Number]="51005")),"EQUIPMENT SALES",AND([Type]="G/L Account",OR([Item Number]="40030",[Item Number]="51012",[Item Number]="62600",[Item Number]="41065")),"EQUIPMENT LEASE",AND([Type]="G/L Account",OR([Item Number]="41010",[Item Number]="51010")),"LABOR INHOUSE",AND([Type]="G/L Account",OR([Item Number]="41015",[Item Number]="51015")),"LABOR OUTSIDE",AND([Type]="G/L Account",OR([Item Number]="41025",[Item Number]="51025")),"SALES TAX",AND([Type]="G/L Account",OR([Item Number]="41030",[Item Number]="51030")),"FREIGHT INCOMING",AND([Type]="G/L Account",OR([Item Number]="41050",[Item Number]="51050")),"CORE",AND([Type]="G/L Account",OR([Item Number]="41055",[Item Number]="51055")),"REPAIR",AND([Type]="G/L Account",OR(TRIM([Item Number])="41031",[Item Number]="51031")),"FREIGHT OUTGOING")

Excelchat Expert
16/03/2018 - 07:48

i understand

User
16/03/2018 - 07:48

Instead of the OR I want a list

Excelchat Expert
16/03/2018 - 07:49

but it you build and manually write everything in the formula then it will be too much manual

User
16/03/2018 - 07:50

sample data

Excelchat Expert
16/03/2018 - 07:51

now what do you want from this data?

Excelchat Expert
16/03/2018 - 07:51

and in which cell

User
16/03/2018 - 07:52

If type="G/L" and Item Number = ("41050,51050) then C4 = "equipment"

Excelchat Expert
16/03/2018 - 07:52

in which cell?

User
16/03/2018 - 07:53

it is a table

Excelchat Expert
16/03/2018 - 07:53

i know

User
16/03/2018 - 07:53

Type and Item Number are column names

User
16/03/2018 - 07:53

Type2, last column is where the formula goes

Excelchat Expert
16/03/2018 - 07:53

yes i know

Excelchat Expert
16/03/2018 - 07:54

what is the requirement in type 2?

User
16/03/2018 - 07:54

return the value based on criteria

Excelchat Expert
16/03/2018 - 07:56

?

User
16/03/2018 - 07:56

The formula I posted is currently in the last column

User
16/03/2018 - 07:56

I was trying to make it more efficient

Excelchat Expert
16/03/2018 - 07:57

what do you want in column 2

User
16/03/2018 - 07:58

If column:Type="Item" and Column:Item Number contains ("41010" or "51010") I want to set Column:Type2 = "EQUIPMENT"

User
16/03/2018 - 08:01

If I can't be done, just let me know.

Excelchat Expert
16/03/2018 - 08:02

have a look at the formula

Excelchat Expert
16/03/2018 - 08:02

it is coming none as none of the columns pasted here have item and number contains 41010 or 51010

User
16/03/2018 - 08:02

Same formula I hae.

User
16/03/2018 - 08:02

I have

Excelchat Expert
16/03/2018 - 08:02

does this formula help?

User
16/03/2018 - 08:03

It is the same thing I have, I was looking get away from the OR

Excelchat Expert
16/03/2018 - 08:03

but why

Excelchat Expert
16/03/2018 - 08:03

this is the best possible solution i think

Excelchat Expert
16/03/2018 - 08:03

it is so simple

User
16/03/2018 - 08:03

OK - thanks for your help.

Excelchat Expert
16/03/2018 - 08:03

and it solves your requirement

User
16/03/2018 - 08:04

It is what I had already ... thanks

User
16/03/2018 - 08:04

appreciate you looking into it.

Excelchat Expert
16/03/2018 - 08:04

welcome

Excelchat Expert
16/03/2018 - 08:04

please do give good rating

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*