Static Multiple Criteria Lookup

[mohitspamz]

Hey!
I'm working on this new worksheet where i'm planning to track Tyres' life as used in our fleet of vehicles.
For this I've made 2 sheets, 1 is the purchase/issue sheet and the 2nd is the tyre maintenance sheet..
Since i need to track tyres, i'll have to use the Serial Nos. of the tyres inscribed on them. What i require is to lookup the serial no. from Purchase/issue sheet to my Maintenance sheet. To do that there'll be 3 criterion to match to get the correct Serial No.
Thanks to Donkey0te and his help to one of th other forum members i found the way to do this using LOOKUP, however there is 1 problem.

One of the criteria to lookup is date and i'm facing a problem here.

In my Purchase sheet suppose if a purchase a tyre and issue it to some vehicle on 11th September, i'll have its first inspection for maintenance after mebbe a month (no fixed intervals). So if i check on 10th October the lookup should actually find the greatest date lesser than the inspection date and keep it to that. I know that the lookup function would get the last unit in case of duplicate entries but that becomes dynamic and the serial no. would change once theres another entry matching the 3 criteria. And dat would mess up my previous record.

I'm not sure if i succeeded in explaining this too well, anyways i'm attaching a sample sheet and i guess that would bring some sense to what i'm trying to achieve.

Thanks again for being such an informative community and a great resource..

Regards

Mohit

[mohitspamz]

Hey!
Bumping the thread up a bit... i can see there r 9 views to the sample sheet i uploaded, but not even a single reply..

Did i fail to explain the problem problem properly? :S

Requesting for some help!

Regards

Mohit

[WHER]

Ok, i'll have a go then, see attachment.
I have to say it seems a bit "reverse engineering" to use data to look up a unique serial number, usually the serial number is used to look up the data?

[mohitspamz]

Hey!
Thanks soo much for replying! I know.... its weird, but somehow this is what i've come up with to track my tyre inventory... Main reason behind this is that the actual serial nos. of these tyres are alphanumeric and are lengthy... so it'll be difficult to get their Serial No. readings while they r still attached to the vehicles in a routine check up... and it'd be great if i could help them out by determining the serial Nos. for them so they dont have to bang their heads under the vehicle body! :P

Btw, ur formula works perfect! I just cudnt get the meaning of it though, if u cud be kind enough to explain it to me...
I thought 2 means the number of values it has to lookup and 1/ab are the criteria..
U've used 2,1/abc

Thanks

Mohit

[WHER]

The main part of the formula ( =lookup(2,1/etc...) was already in place in the attachment you posted in post #1, courtesy of Donkey0te as you mentioned, so the functioning of the formula remains as it was before. I just added one extra condition ('Tyre Purchase Report'!$E$2:$E$40<'Tyre Maintenance Report'!A2)

[mohitspamz]

Yeah, so there are 3 conditions to lookup but formula starts with 2... wot is the significance of the first =lookup(2,....) ?? shudnt it be 3 as we are now looking up 3 values?

[WHER]

No, as far as i understand it myself, and correct me if i'm wrong, =lookup(2,1/...) means we're looking up the number "2" in an array of values, made up from 1/0's( 0's resulting from "false" conditions) and 1/1's ( lower 1's resulting from "true" conditions). The 1/0's result in "div/0"errors, the 1/1's result in 1's. The lookup looks for the number "2", doesn't find it, and returns the last 1 in this "lookup vector" array, which returns the corresponding value in the "result vector" array, in our case "Tyre Purchase Report'!$C$2:$C$40".

[DonkeyOte]

No, as far as i understand it myself, and correct me if i'm wrong, =lookup(2,1/...) means we're looking up the number "2" in an array of values, made up from 1/0's( 0's resulting from "false" conditions) and 1/1's ( lower 1's resulting from "true" conditions). The 1/0's result in "div/0"errors, the 1/1's result in 1's. The lookup looks for the number "2", doesn't find it, and returns the last 1 in this "lookup vector" array, which returns the corresponding value in the "result vector" array, in our case "Tyre Purchase Report'!$C$2:$C$40".

Quite correct - I tried to expand on this approach here: http://www.excelforum.com/2100732-post14.html which may or may not help...

[mohitspamz]

Hey!
Thanks for the link and the explanation! Nicely done and a good resource to understand lookup function...
Cudnt get anything about lookup from F1 in excel.. :P