Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Double Lookup Without Exact Match-Price Breaks
Hello,
I have a table that looks like this -----A------------B-----------C------- Part Number------Qty-------Price Each 1----Part 1---------2-------------23.01 2----Part 1--------40------------8.76 3----Part 1--------80-----------8.39 4----Part 2--------1-------------58.12 5----Part 2--------20-----------9.04 6----Part 2--------40-----------7.74 7----Part 3--------1------------60.00 8----Part 3--------20----------9.04 9----Part 3--------40----------7.74 And so on.... These are price breaks. I'd like to enter the Part number in cell G1 and the Qty in cell H1 and have the formula/array lookup the Price Each. I searched the newsgroups and found the following article about a "double lookup": http://www.mvps.org/dmcritchie/excel/vlookup.htm ...and used the following formula: =INDEX(C2:C10,MATCH(1,(A2:A10=G1)*(B2:B10=H1),0)) This works great if the quantity is an exact match. If I input Part 2 Qty 20, I get $9.04. However, if I enter Part 2 Qty 21, I get an N/A since there isn't an exact match I thought I could change the match type of the array (the last 0) to an approximate match, but I either get a N/A when I change it to -1 or 0, or the last value in column C if I change it to 1. Can I change this array so I can get an approximate match so if I enter Part 2 Qty 21, I get $9.04 or Part 2 Qty 3 $58.12? Any help would be greatly appreciated. Thank You, -Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get exact match with LOOKUP function? | Excel Worksheet Functions | |||
Vlookup- Closest match that contains the exact lookup value? | Excel Worksheet Functions | |||
Lookup Exact Match | Excel Worksheet Functions | |||
Lookup and Match with not exact numbers | Excel Worksheet Functions | |||
Match - Exact - Lookup? | Excel Worksheet Functions |