Logical Functions corresponding to Lists

Copper Contributor

Cell A1 has a list in it = Example 1, Example 2, etc. 

I want A2 to change values depending on the chosen item from the list of A1.

I know I can insert =IFS(A1="Example 1",001,A1="Example 2,002.........)

But this will take a long time and I have a lot of "examples" to insert. Is there a way instead of typing out the name, to instead have a function that states if the A1 list position=1 (ie Example 1) then A2=001 ?

3 Replies

@TheAnalogKid 

That could be like

image.png

with

=INDEX(
  FILTERXML("<t><s>"&SUBSTITUTE(A2, ",", "</s><s>")&"</s></t>","//s"),
  MATCH(C1,
        FILTERXML("<t><s>"&SUBSTITUTE(A1, ",", "</s><s>")&"</s></t>","//s"), 0)
)

 

@Sergei Baklan 

 

Thanks. I think the error is my own, I was to generic when stating my issues. Ill try to be more specific.

 

A1 has the following Drop down List:
Mop

Bucket

Rag

Cleaner

 

I need to create a price list in A2. I know I can insert the following IFS function

=IFS(A1="Mop",5,A1="Bucket",10,A1="Rag",1,A1="Cleaner",2)

Which will get me the desired result. Here is my issue:

 

I have 100s of items to log and I do not want to type the IFS statement when the descriptions are detailed...ie =IFS(A1="Infrared Thermography Camera",495,A1="......etc)

 

All the descriptions and pricing is logged on another sheet and I have made lists already by selecting ie. B3:B140

 

So what function is there that will state: when A1 dropdown list is selected position 1, A2 will equal #

 

Thanks

@TheAnalogKid 

If you have table with prices formula could be

image.png