How to get the corresponding name of executive from range of receipts issued

[purav82]

Hi,

I have a problem in which each executive is issued a receipt book which is serially numbered. So for eg. Executive A is issued receipt book from containing receipt no. from 1001 to 1100. Like wise Executive D is issued receipt book containing receipt no. from 1301 to 1400.

So if we get receipt no. 1050 then in the corresponding column name of Executive A should appear as receipt no. 1050 is with A. Like wise if receipt no. is 1349 then name of Executive D should appear as receipt no. 1349 is between receipt no. 1301 to 1400 which was issued to Executive D.

Like this we more than 60 Executives to whom hundreds of receipts are issued.

I have attached a sample file.

Kindly help me out on this.

Thanks in Advance.

[AliGW]

In B17 copied down:

=INDEX($C$3:$C$9,MATCH(A17,$A$3:$A$9,1))

[purav82]

Wow Ali Thanks for replying so quickly.

Your suggestion almost worked, but there is one problem. If i enter receipt no. greater than 1700, say for eg. 1851, which is actually not in my receipt range, then also the name of Executive G is appearing. In fact the formula should return error #N/A.

If you can sort this out it would be very helpful.

Thanks in advance.

In B17 copied down:

=INDEX($C$3:$C$9,MATCH(A17,$A$3:$A$9,1))

[AliGW]

Try this:

=IF(A17>$B$9,"N/A",INDEX($C$3:$C$9,MATCH(A17,$A$3:$A$9,1)))

or this:

=IF(A17>MAX($B$3:$B$9),"N/A",INDEX($C$3:$C$9,MATCH(A17,$A$3:$A$9,1)))

[Glenn Kennedy]

tRY THIS INSTEAD:

=IFERROR(INDEX($C$2:$C$9,MATCH(1,INDEX(($A$2:$A$9<=A17)*($B$2:$B$9>=A17),0),0)),"")

[purav82]

Thanks Ali.

Both formula are working perfectly for me.

I'll take some time to understand it, but still working fine.

Thanks again.

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[purav82]

Thank your very much Glenn.

I have tried formula also. The only thing is that it does not return Error if the receipt no. is not in your range. Other wise the formula is working perfectly fine.

A bit complicated formula for me. I need some time to digest it.

But anyways, Thank you very much Glenn.

[Glenn Kennedy]

I know. Virtually no-one wants to see the error message. It's designed to leave blanks INSTEAD of errors.

=INDEX($C$2:$C$9,MATCH(1,INDEX(($A$2:$A$9<=A17)*($B$2:$B$9>=A17),0),0))

will leave the #N/A error in.

[purav82]

Got it.

Thanks for replying so quickly. It was like Real time reply.

Thanks Glenn

[purav82]

Hi,

I have a further question to my query.

If the receipt nos. are not in sequence, some receipt nos. are missing then how to get the correct result.

I have attached a sample file in which you can see that the receipt nos. 1200 to 1300 are missing. Like wise receipt no. 1401 to 1449 are also missing.

Thanks in Advance.

[AliGW]

You need to add them to your lookup list but leave the manager list blank.

[Glenn Kennedy]

...or use the formula that I already gave you.

=INDEX($C$2:$C$9,MATCH(1,INDEX(($A$2:$A$9<=A12)*($B$2:$B$9>=A12),0),0))

[purav82]

Got it. Simple & easy.

Thank you very much.

You need to add them to your lookup list but leave the manager list blank.

[purav82]

Works perfectly fine.

Thanks Glenn.

...or use the formula that I already gave you.

=INDEX($C$2:$C$9,MATCH(1,INDEX(($A$2:$A$9<=A12)*($B$2:$B$9>=A12),0),0))

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.