Need Help with a Conditional Multiple Lookup in Excel 2010

[3PLAnalyst]

Hello All,

This is my first posting so please bare with me. I have some customer orders placed on various days going to a variety of different US states. However, we only ship to certain states based on our own shipping schedule or matrix. I have the shipping matrix on one sheet and the customer orders on a separate sheet. I want to be able to look up the destination state of the customer order on the matrix and then choose the closest matching matrix date from a list of 5 possible ship dates. I am modeling this data so for my purposes the closest matching date can be in the past. I have thought about and tried using VLOOKUP, INDEX/MATCH, CHOOSE, and so forth but I'm just getting myself confused. Can someone point me in the right direction? Thank you.

[TABLE="width: 288"]


<colgroup><col style="width: 48pt;" span="6" width="64">
<tbody>
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"][/TD]

[/TR]


</tbody>[/TABLE]

 

[CalcSux78]

VLOOKUP is a very bad habit to get into. There are limits as to what it can do and what it will return. With Index-Match, the possibilities are far greater.

In the cell your answer will be placed, insert formula:

=INDEX(Reference<range-of-potential-results>,MATCH(Row_Num<value-to-find>,Column_Num<range-to-search>,0),0)</range-to-search></value-to-find></range-of-potential-results>

Where:
Index(Reference<reference>,Row_Num<row_num>,Column_Num<column_num>)
Match(Lookup_Value<lookup_value>,Lookup_array<lookup_array>,Match_type<match_type>)

Example: (where EstimateData reference to table on separate sheet)

=INDEX(EstimateData[CATNUM],MATCH(A49,EstimateData[CAT],0),0)

-Or-
If you have a table in B3:C10 and the data to be matched is in A1, data to be returned is in column C:

=INDEX(C3:C10,MATCH(A1,B3:B10,0),0)

The order of the index function can be changed to: INDEX(Reference<reference>,0,MATCH(Match parameters)) to make it an HLOOKUP.</reference></match_type></lookup_array></lookup_value></column_num></row_num></reference>

 

[3PLAnalyst]

Thank you for the quick response. I've never used INDEX/MATCH before so I'm still confused. I think it would help if I could see how it works with my actual data. Is there a way I could send you the workbook? I hope I'm not asking too much but this is new to me and I want to get it right.