Multi Condition Lookup

[GB]

Hi,

I was reading the post on "multi condition lookup" but I could not manipulate to do the following...


I am working with a time conversion and I have three columns of data (Place, Date, VarGMT). An example of the data is...


Place Date VarGMT

LAX, 14Mar2010, -7

LAX, 07Nov2010, -8

LAX, 13Mar2011, -7


What I am trying to do is query on Place & Date to give the VarGMT. For example, find LAX & 01Dec2010 and give me -8. My problem is that 01Dec2010 is not in the list so it needs to find the previous date before this query date. Note the data is ordered by Place, Date (ascending).


Any help will be appreciated.

cheers

GB

 

[NARAYANK991]

Hi GB ,


Try this :


=LOOKUP(G2&H2,A2:A4&B2:B4,C2:C4)


I have assumed your data is in the range A2:C4 , with the headers in row 1. The lookup values LAX and 01Dec2010 are entered in G2 and H2. Make the addresses absolute as required.


Narayan

 

[GB]

Hi Narayan,

your formula doesn't work. I have attached the spreadsheet so you can see. I would appreciate it if you can have a look.


https://dl.dropbox.com/u/60464004/Excel/MultipleLookup.xlsx


regards

GB

 

[NARAYANK991]

Hi GB ,


You had mentioned the following in your original post :


Note the data is ordered by Place, Date (ascending).


The above is not the case in the file you have uploaded. Can you clarify ?


Narayan

 

[shibulal]

Hi Narayan,I checked it with the same data.

And it gave me the desired result.


Though I didn't get how the formula is worked.

 

[GB]

Hi Narayan,

sorry I didn't order as suggested. I re sorted the file and the formula works, thanks. If I left it in its current sort I assume a different approach would be required. Can you suggest something?


cheers

GB

 

[NARAYANK991]

Hi GB ,


If the data is not sorted , then an INDEX & MATCH would be required. Try this :


=INDEX(VarGMT,MATCH(G1,Place,0)-1+MATCH($H$1,OFFSET(Date,MATCH(G1,Place,0)-1,0,COUNTIF(Place,G1),1)))


Narayan

 

[GB]

Thanks Narayan, that really helps.

cheers

GB