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.
