look up with multiple criteria and within a range

[Guest]

I need to look up information using multiple variables, one of those
variables using a range. I want the associate number returned based on where
they fall in the sequence and batch number. The following is an example:

SHEET ONE:
Sequence Start Sequence End Batch Associate
07953 07988 322 201
07989 08013 322 247
41222 41238 213 232
00238 00252 001 228
SHEET TWO:
Sequence # Batch Associate
07999 322 ??????

Based on this example the associate number that should be returned is 247.
Thank you all in advance for your help!!!!

 

[Guest]

Hi,

If the data is for example in a2:d5 of sheet1 and the sequence and batch to
be checked are in a2 and b2 on sheet2, one way to retrieve the associate
number is:

=SUMPRODUCT(--(A2>=Sheet1!A2:A5),--(A2<=Sheet1!B2:B5),--(B2=Sheet1!C2:C5),Sheet1!D25)

(assuming the sequences in the data range are unique)

Hth
Anthony

 

[Guest]

Thanks for your reply Anthony. Only the sequence AND batch combination would
be unique. They sequences may repeat themselves and the batches may also.
However, the combination of both would never be the same. Please advise.
Thanks!

 

[Guest]

Many thanks for your feedback, glad to be of help.
If the sequence and batch combinations are unique then this will work fine
as the
formula evaluates the combinations and only the matching one is returned.
(If none match it retuirns 0).

Anthony