CODEWITHSUNDEEP
excelexcel-formulalookup
Haja
Haja

Reputation: 11

Autopopulate another cell with a specific value from a drop down Liat

I select an input on cell L10 using a drop down list. The values of the list are AH11, AH12, AH13, AH14, AH15 and AH16

Base on this selection, I want to auto populate the value in another cell.

I used the following formula in my target cell 

=IF(OR(L10="AH11",L10="AH12"),"6",IF(OR(L10="AH15",L10="AH16"),"18"))

This works because AH11 and AH12 have the same values. Similarly for AH15 and 16.

But AH13 and AH 14 have their unique values.

How do I improve the formula to display values for AH13 and AH14 also?

Upvotes: 1

Views: 58

Answers (2)

user4039065
user4039065

Reputation:

Build an INDEX/MATCH using array constants instead of cell ranges.

=index({6, 6, 99, 100, 18, 18}, match(L10, {"AH11", "AH12", "AH13", "AH14", "AH15", "AH16"} , 0))
'with these progressive lookup values it can be shortened to,
=index({6, 99, 100, 18}, match(L10, {"AH11", "AH13", "AH14", "AH15"}))

I don't recommend that you return quoted text-that-looks-like-numbers. While there are limited special cases where this is desirable, it is almost always better to leave numbers as true numbers.

Upvotes: 0

Nick
Nick

Reputation: 147216

Just nest the IF's further:

=IF(OR(L10="AH11",L10="AH12"),"6",IF(OR(L10="AH15",L10="AH16"),"18", IF(L10="AH13", "xx", IF(L10="AH14","yy"))))

Upvotes: 1

Related Questions