# Excel - IF Function Problem - Expert Solution

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
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
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
User 16/03/2018 - 08:04
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

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.