Forum Discussion

TheAnalogKid's avatar
TheAnalogKid
Copper Contributor
Sep 11, 2021

Logical Functions corresponding to Lists

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.......
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 11, 2021

TheAnalogKid 

That could be like

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)
)

 

TheAnalogKid's avatar
TheAnalogKid
Copper Contributor
Sep 11, 2021

SergeiBaklan 

 

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

Resources