Vlookup and return value from a range of results?

Hi All,

Would appreciate any help with this dilema.

Reference Sheet
Contains the Customer ID, Membership Level and Membership Expiry

Working Sheet
Contains the Customer ID, Access Date and Calculation Column.

Required:
I am trying to return the Membership Level, for a given Customer, at a particular Access Date (row) from the Reference Sheet.

The expiry dates in the Reference Sheet is the upper limit. More than one line per customer means their Membership has changed and 31/12/9999 is current.

I hope this makes sense and please let me know if I need to provide more info.

Thanks again
Randy

Does this work for your needs?

=LOOKUP(2,1/((REFERENCE!$A$2:$A$13=A2)*(REFERENCE!$C$2:$C$13>=B2)),REFERENCE!$B$2:$B$13)

copied down

Does each customer have a 9999 expiry membership?

If the access date for 3000790344 is after 27/06/09 do you want the GVI_GDOM?

That's correct, all would have a expiry of 31/12/99. Just like this forum here, they're all members for life =)

The results I'm hoping for are this:
EVI_BUS24
EVI_BUSTR
EVI_09_E_P
EVIOB10DAY
GVI_09_DOM
EVI_09_HOM
EVI_09_HOM
EVI_09_DHP
EVI_09_DHP

Thanks

The WORKING sheet is essentially a log, where it records Customer Access and the membership level that would have been valid at that point in time.

try:

=INDEX(REFERENCE!$B$2:$B$13,MATCH(1,INDEX((REFERENCE!$A$2:$A$13=A2)*(REFERENCE!$C$2:$C$13>=B2),0),0))

Are there exact matches for each ID (if so, make sure there are no extra spaces, etc that may not make them exact).

Do all the IDs have a 9999 date.. because the formula is looking for first date over the accessed date for the respective ID, if it doesn't find a date that is larger, then it will return error.

There are exact matches to all IDs in the REFERENCE sheet and all will have the 9999, just depends on how many they have before that.

Thanks for your help NBVC, its late here (2am) and I will play with it more at work tomorrow. I know it works on the Sample file, so just need to replicate across.
PS. My Excel crashed when trying to do this for 50,000 lines lol..

It is an array formula, unfortunately, and array formulas are resource-intensive.

If there is anyway you can reduce the range from 50000 to the max necessary, it will help.

I am trying to figure out if it is possible to apply helper columns which would increase the efficiency.... but not sure if it possible given the criteria we are trying to match up...