CODEWITHSUNDEEP
excelexcel-formulalookup
Aleksey Napolskih
Aleksey Napolskih

Reputation: 123

Two-Dimensional Lookup in Excel 2010

I have a data that looks similar to this

Tables contains more than one row label heading (stacked row label):

                     |20%     |30%  |
|25/01/11   |buy     |1       |1.1  |
|           |sell    |0.8     |0.9  |
|27/01/11   |buy     |1.02    |1.03 |
|           |sell    |1.1     |1.2  |
|01/02/11   |buy     |1.05    |1.07 |

I am trying to use a lookup formula to retrieve data based on the date and type of transaction: say what was the multiplier on 30% of sell bid on 27/01/11 Any help will me much appreciated!

UPD

there has to be a solution to this without adding a unique column against which to lookup... Excel 2000 had a way of doing it by using natural language formulas as you can see from this article in method 2, example 2 - support.microsoft.com/kb/275170

UPD 2

Is it possible at all? the dates will not only have gaps, but will also be skipping one or two days... just like in the example

UPD 3

enter image description here

There are problems with the solutions posted so far:

if the lookup date is 26/01/2011 the formula will return N/A (in the perfect solution it should return the closest match (rounded down to previous date) - i.e. if the lookup date is not available in the column A then the previous closest date should be returned)

stacked row header means that i have rows arranged into groups like this (date and type being the row headers) enter image description here

Upvotes: 3

Views: 2282

Answers (4)

chris neilsen
chris neilsen

Reputation: 53135

If F1 contains the requierd date, F2 the %, F3 "buy" or "sell"

=INDEX($C:$D,MATCH(F1,$A:$A,0)+IF(F3="sell",1,0),MATCH(F2,$C$1:$D$1,0))

takes all three factors into account

EDIT

based on your edit, to get what you want you only need to change the exact match parameter from 0 to 1. From Excel help file

Match_Type: 1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order

so formula becomes

=INDEX($C:$D,MATCH(F1,$A:$A,1)+IF(F3="sell",1,0),MATCH(F2,$C$1:$D$1,0))

Also, if you want to do the same with the % values, do the same to the other MATCH too

=INDEX($C:$D,MATCH(F1,$A:$A,1)+IF(F3="sell",1,0),MATCH(F2,$C$1:$D$1,1))

Upvotes: 4

vasek1
vasek1

Reputation: 14071

In these situations, I find it helpful to include a "key" column on the left of the data, which simplifies the formulas a great bit. Insert a column to the left of your data and have it be the combination of date and buy/sell (A2 = B2 & "_" & C2):

                                   |20%     |30%  |
25/01/11_buy  |25/01/11   |buy     |1       |1.1  |
25/01/11_sell |           |sell    |0.8     |0.9  |
27/01/11_buy  |27/01/11   |buy     |1.02    |1.03 |
27/01/11_sell |           |sell    |1.1     |1.2  |
01/02/11_buy  |01/02/11   |buy     |1.05    |1.07 |

Then, just do a vlookup + match:

=VLOOKUP({needed date and type}, A1:E6, MATCH({needed %}, A1:E1,0),0)

The advantage of this approach is that it keeps the formulas simple and easy to read

Upvotes: 0

Suresh
Suresh

Reputation: 1091

Consider you have the values starting from A1. The date 25-01-2011 will be at A2, 27/01 will be at A4 etc.20%, will be at C1 and 30%, will be at D1

Put the look up date u want in F1, look up percentage u want in G1(20 or 30%), look up type u want in H1(buy or sell)

Enter the below formuka in I1. You have the result.

=INDEX(C2:D6,IF(H1="buy",MATCH(F1,A2:A6,0),IF(H1="sell",MATCH(F1,A2:A6,0)+1)),MATCH(G1,C1:D1,0))

Upvotes: 0

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

Assuming your data starts in Column A and your lookup date is in cell F1, try this:

=INDEX(D:D, MATCH(F1,A:A,0) + 1, 1)

This works by finding the row that matches the date (MATCH), offsets it by 1 to get the sell row, and uses the INDEX function to retrieve the value from Column D.

Upvotes: 1

Related Questions