Thread
:
Double Lookup Without Exact Match-Price Breaks
View Single Post
#
2
Posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
Posts: 35,218
Double Lookup Without Exact Match-Price Breaks
You have a response at your other post.
wrote:
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
--
Dave Peterson
Reply With Quote
Dave Peterson
View Public Profile
Find all posts by Dave Peterson