Less cumbersome multiple lookup

drluke

Active Member
Joined
Apr 17, 2014
Messages
283
Based on the data I have in col B, I am attempting to insert a code in col A. Any one of 4 different data items could be in col B.

Is there a formula that is less cumbersome than IF B=Water then A=Bottle or IF B=DAY then A=NIGHT etc... Any advise greatly appreciated!!

COL ACOL B
Formula = Bottle or NightCould be any of Water or Day

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

becci.gott

Board Regular
Joined
Oct 2, 2012
Messages
74
Is this what you have at the moment?
=IF(B1="Water","Bottle",IF(B1="Day","Night"))

Would it be better to use a VLOOKUP?
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
You can also use a LOOKUP function:
Code:
=LOOKUP(B1,{[COLOR=#ff0000]"Day","Water"[/COLOR];"Night","Bottle"})
Add you can keep adding to that (you said that you have four possible values).
Just note that the values in red (the values you are matching on) must be in alphabetical order.
For more on the LOOKUP function, see: https://www.techonthenet.com/excel/formulas/lookup.php
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,401
Members
409,871
Latest member
i1patrick
Top