CODEWITHSUNDEEP
excelexcel-formula
konrad
konrad

Reputation: 3706

fill data in excel by key: value using formulas

I have a column that contains rows:

A1 = Typical
A2 = Typical
A3 = Expressed
A4 = Expressed
A5 = 3600
A6 = Typical

Where Typical is equal to 3800 and Expressed is 5000

I tried setting D4 = Typical, E4 = 3800 and D5 = Expressed while E5 = 5000 as my key:value cells. I then tried something like this: 

=IF(ISNUMBER(SEARCH(D4,A1)), E4,IF(ISNUMBER(SEARCH(D5,A1)), E5,A1))

Is there a way to make this formula variables for D4, E4, D5, E5 "stick" while A1 can be replaced with A2-A6?

I am expecting a result where B1-B6 is like so:

B1 = 3800
B2 = 3800
B3 = 5000
B4 = 5000
B5 = 3600
B6 = 3800

Any ideas are welcome.

Upvotes: 0

Views: 578

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

Something like: (in B1 and copy down)

=IFERROR(LOOKUP(A1,$D$4:$D$5,$E$4:$E$5),A1)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

In B1 enter:

=IF(ISNUMBER(A1),A1,IF(A1=$D$4,$E$4,$E$5))

and copy down:

enter image description here

Upvotes: 1

Related Questions