2 Dimensional Lookup Problem 2

[mcongdon]

Hi Guys,
I need to do a lookup function for a table. Everything I could find to answer my question was different than what I need to do. I have a table of ticket charges for stock trade ranges. On the horizontal axis there is the stock price and on the vertical it is the number of shares. I have data for 65,000 trades (just shares and price). I want to create a column to lookup what the charge would be for each one. For example:

The table of charges:

5 10 25 50

10 15.65 15.85 16.35 16.75

25 16.03 16.67 17.23 18.30

50 16.48 17.12 18.20 19.88

(Once again, price is on the horizontal axis and number of shares is on vertical.)


I want to be able to lookup the charge for a trade with:
8 Shares with a price of $8

-It should return the number 15.85 from the table (1st row, 2nd column)

or

13 Shares with price of $39
should return $17.23

The real table is much larger, but if I have an idea on how to do this, I can figure it out for the larger version.
Thanks!

 

[Rofeu]

I think you need to use an INDEX combined with 2 MATCH functions. Something like:

=INDEX(b2:ZZ65536,MATCH(Shares,A:A,0),MATCH(Price,1:1,0))

Cheers,

Roel

 

[mcongdon]

I keep getting a #N/A or a completely different number than the one I'm looking for. Could you clarify what the B2:ZZ65536 is referring to?

 

[Rofeu]

Hi,

sorry, I just typed that in as a starter. The b2:ZZ65536 should basically be your entire dataset.

Cheers,

Roel

 

[mcongdon]

Okay, that is what I figured it meant, just clarifying. I ran the error checking and calculation steps on the formula and the match function is trying to match exactly with the price and share amounts. I need it to look in ranges. so the table should look more like this I guess:

$0-4.99 5-9.99 10-24.99 25-49.99

0-9 15.65 15.85 16.35 16.75
10-24 16.03 16.67 17.23 18.30
25-49 16.48 17.12 18.20 19.88
50-74 16.84 17.70 19.27 20.15

Therefore a trade of 13 shares at a price of 7.50 should return 16.67
Thanks

 

[mintjulep]

The last argument of MATCH is match_type.

I think you want a 1, which is "MATCH finds the largest value that is less than or equal to lookup_value

 

[mintjulep]

Sorry, -1 would be what you want.

"If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value

 

[AnotherHiggins]

Could you confirm that the second example in your first post is wrong? 13 Shares at $39 should be 18.3, right?

If so, then you'll need to sort your lookup list DESCENDING by both rows and columns. (Data > Sort > Options will give you the option of sorthing left-to-right).

Then use Match_Type -1.

I'll replace the ranges with descriptions of what ranges to use:
[tab][COLOR=blue white]=INDEX(LookupTable, MATCH(NumberOfShares, First[red]Column[/red]OfLookupTable, -1), MATCH(PriceOfShares, First[red]Row[/red]OfLookupTable, -1))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mcongdon]

John,
That worked perfect, you were dead on about the mistake I made in my post. Sorry about that. Thanks a ton!
-Mike

 

[AnotherHiggins]

No problem. I sat here for several minutes trying to figure out the logic that would produce the results for the two examples you gave. I'm glad I wasn't just missing something obvious!

Glad you got it worked out.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.