Find a number from multiple range of numbers

[spycein]

Dear All,
I have two tables as mentioned below

Master Date Table No.1:-

[TABLE="class: grid, width: 413, align: left"]
<tbody>[TR]
[TD="align: center"]Serial No.[/TD]
[TD="align: center"]Quantity of Cards[/TD]
[TD="align: center"]Start No.[/TD]
[TD="align: center"]End No.[/TD]
[TD="align: center"]Invoice No.[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]193201[/TD]
[TD="align: center"]193700[/TD]
[TD="align: center"]1A[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]199701[/TD]
[TD="align: center"]200700[/TD]
[TD="align: center"]1B[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]214301[/TD]
[TD="align: center"]215300[/TD]
[TD="align: center"]1C[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]223101[/TD]
[TD="align: center"]224100[/TD]
[TD="align: center"]1D[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]235101[/TD]
[TD="align: center"]236100[/TD]
[TD="align: center"]1E[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]239601[/TD]
[TD="align: center"]240600[/TD]
[TD="align: center"]1F[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]242601[/TD]
[TD="align: center"]243600[/TD]
[TD="align: center"]1G[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]990[/TD]
[TD="align: center"]243611[/TD]
[TD="align: center"]244600[/TD]
[TD="align: center"]1H[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]262501[/TD]
[TD="align: center"]263500[/TD]
[TD="align: center"]1J[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]275201[/TD]
[TD="align: center"]276200[/TD]
[TD="align: center"]1P[/TD]
[/TR]
</tbody>[/TABLE]

















Search Result Table No.2

[TABLE="class: grid, width: 140"]
<tbody>[TR]
[TD="align: center"]Card No[/TD]
[TD="align: center"]Invoice No[/TD]
[/TR]
[TR]
[TD="align: center"]214449[/TD]
[TD="align: center"]1C[/TD]
[/TR]
</tbody>[/TABLE]

I am looking for formula which would look up the card number in the entire range in table no.1 and return the invoice no.

Awaiting for awesome solutions.

Best Regards,

Shib

 

[Fluff]

How about
=INDEX(Sheet1!E2:E11,MATCH(1,(A2>=Sheet1!C2:C11)*(A2<=Sheet1!D2:D11),0))

This is an array formula & needs to be confirmed with Ctrl Shift Enter, rather than just Enter

 

[mikerickson]

It looks like the StartNumber column is sorted ascending. If so (with "Start No." in Sheet1!C1 and Card No" in Sheet2!A1)

=VLOOKUP(Sheet2!A2, Sheet1!$C$1:$E$100, 2)

 

[spycein]

Thank you so much Fluff. Exactly what i needed.
Works like a charm.
Many Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

For future reference
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Cross posted https://www.excelforum.com/excel-general/1288431-find-a-number-from-multiple-range-of-numbers.html