Excel 2007 : Nesting VLookup Function or alternative?

[scoman]

So I have a sheet where there is an item code in a column... within that item code is a range of zip codes in another column, and next to each zip code is a corresponding price.

What I am trying to accomplish is to have one cell where you can enter the item code, and another cell where you can enter the zip code, and the appropriate price will show up.

I have been going crazy trying to nest a vlookup within a vlookup where I can get an exact match to the item code, and a ranged match to the zip code (since we only look at the first three numbers) and have been having zero luck figuring this out.

I've attached a basic version of the file which should give you an idea.

Any thoughts?

[NBVC]

Name each table,

e.g.

Select C1:G7 and call it J1_Table (by entering the name in the Name Box to the left of the formula bar).

Do similar for each table, i.e. J2_Table and J3_Table

then use formula:

=LOOKUP(2,1/(INDEX(INDIRECT(J5&"_Table"),0,1)<=K5),INDEX(INDIRECT(J5&"_Table"),0,2))

copied down

[pmalen]

Another option...

This is also another way to overcome SUMIF limitations. you can have an almost infinite number of SUMIF criteria using this method.