Two-Dimensional Lookup in Excel 2010

3
votes

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

4
excelexcel-formulalookup
is there always one buy and one sell for each date in that order? - barry houdini
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 ... - Aleksey Napolskih
At least two solutions have been posted that do not require a unique column and work accurately for the data in your example. Please accept one of these answers or edit your question to be specific as to why these solutions don't work for your actual data. - Rachel Hettinger
sorry, i must have described my problem unclear. main post updated. - Aleksey Napolskih

4 Answers

4
votes

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))
1
votes

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.

0
votes

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))
0
votes

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