excel 2003 look up formula

**Grumpy9215** · 10-27-2009, 02:03 PM

I have 3 different costs for an item, 15 different items. The costs very depending on the delivery state. Is it possible to write a formula that will retrun the right cost for an item, depending on the delivery state?

if ap10 = "CA" then look up in range AH2 to AH18
if ap10 = "GA" then look up in range AI2 to AI18
if ap10 = "ME" then look up in range AI2 to AI18
if ap10 = "PR" then look up in range AI2 to AI18
if ap10 = any other state then look up in range AG2 to AG18

cell AP10 being the cell that the state is listed in
Cell U10 being the cell that item is in (a drop down menu)

This is the vlookup formula that will get one price.

=IF(ISNA(VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2,FALSE)),"",VLOOKUP($U10,TABLE!$AF$2:TABLE!$AI$18,2,FALSE))

**DonkeyOte** · 10-27-2009, 02:43 PM

If I've understood you perhaps:

=INDEX($AG$2:$AI$18,MATCH($U10,$AF$2:$AF$18,0),IF($AP10="CA",2,IF(OR($AP10={"GA","ME","PR"}),3,1)))

**JBeaucaire** · 10-27-2009, 02:45 PM

It's possible to restructure a very long and ugly IF statement to take care of that, but when you're evaluating the SAME cell over and over again, there's usually a better way.

Click GO ADVANCED and use the paperclip icon to post up your workbook. Let us look at some examples of what you're doing and I'm sure something more appropriate will be possible.

EDIT:....if Don's answer doesn't do it for you.

**Grumpy9215** · 10-27-2009, 03:06 PM

I've attached the zipped file. The states are in a drop down menu in column AP. there are 3 diiferent cost for each retrofit package - the diiference in cost depends on which state is the final destination. The look up table is on the sheet "TABLE".

**JBeaucaire** · 10-27-2009, 03:49 PM

Don nailed it then, just missing the sheet references. Put this in V10 and copy down:

=INDEX(TABLE!$AG$2:$AI$18, MATCH($U10, TABLE!$AF$2:$AF$18, 0), IF($AP10="CA", 2,IF(OR($AP10={"GA","ME","PR"}), 3, 1)))

Also, you don't need to error check the column U entries if you use a data validation list in column U that actually comes from the TABLE sheet. This eliminates errors in two separate tables.

Just highlight TABLE!AF2:AF18 and type RetroFitOptions into the NAME BOX (just to the left of the formula bar). Then in column V of the BUY SHEET use the data validation source of =RetroFitOptions.

Now, if you edit/remove items from the list on TABLE, the data validation list instantly changes too.