Look a number or value between a series

[Tayyab Hussain]

Dear All-

I am looking for a solution either VBA or a function() that would look up for a number that would match the first 4 numbers in a serial.
eg
Sheet 1
Ticket_Nos Last_ticket_Used
124001 124082
124101 124145 anbd so on
124201
.
.
124901

Sheet 2 or Workbook 2
date Ticket_from Ticket_to
01/07/2015 124001 124034
01/07/2015 124101 124126
02/07/2015 124035 124082
02/07/2015 124127 124145
Sheet 1 whould look up for '1240' in sheet2 or WB2 and when the max number is searched in sheet 2 or WB2 it should return the value on sheet 1 under Last_Ticket_Used.

I hope I have cleared my point.

Regards
Tayyab Hussain

 

[Deepak]

Hi,

It's good idea to share the sample with manual calculation.

Check this.

=IFERROR(LOOKUP(2,1/(LEFT(Table1[Ticket_from],4)=LEFT(A2,4)),Table1[Ticket_to]),0)

 

[Deepak]

& If without table.

=IFERROR(LOOKUP(2,1/(LEFT(Sheet2!$B$2:$B$5,4)=LEFT(A2,4)),Sheet2!$C$2:$C$5),0)

 

[Tayyab Hussain]

Dear Deepak,
Thanks for the help, it did work out. Now, Please do explain what does the 2,1 does??? Thanks

 

[Deepak]

For the while google it.
I will explain the same tommorow as reading on mobile.

 

[Chihiro]

My attempt at explanation. Deepak please correct me if I'm off.

LOOKUP(n,LookupArray)
Will return last numerical value from LookupArray, if n does not/cannot occur in LookupArray.

By extension...
LOOKUP(n,LookupArray,ResultArray)
Will return last numerical value from ResultArray which is associated with last numerical value in LookupArray.

So LOOKUP(2,1/LookupArray) uses following logic.
1 divided by any number will be less than 2. Therefore 2 will never occur in LookupArray. It will return last numerical value from LookupArray no mattar what the numerical value is.

Edit: Changed last part a bit.

 

[Deepak]

In addition to what Chihiro said.
Best part of the lookup is that it's ignore the non numeric values & find the largest match, i.e =< the lookup value.

 

[NARAYANK991]

Hi ,

See this link :

Need help to understand formula

Narayan