Lookup return occurrence value 1st,2nd and 3rd

Hi Experts Good Morning,

How do return lookup value Occurrence 1st,2nd and 3rd corresponding to the range and date against from sheet to sheet.

Multiple occurrences of the Lookup value, the formulas searches the first occurrence of the Lookup value, and returns the corresponding value in the same row from another column.

Herewith attached file.Please your suggestion.

DATA:

DISPATCH **** NAME**** SL.NO

11/01/2016**** JALA******11899
11/01/2016**** GOA******11900
11/01/2016**** GURG**** 11905
11/01/2016**** MUMB****11907
11/01/2016**** GOA***** 11913
11/01/2016**** GURG**** 11914

13/01/2016**** AHME**** 11939
13/01/2016**** JAIP***** 11951
13/01/2016**** BANG**** 11948
13/01/2016**** JAIP***** 11952

14/01/2016**** CHEN**** 11959
14/01/2016**** BANG**** 11958
14/01/2016**** KOLK*****11966
14/01/2016**** KOLK*****11969
14/01/2016**** CHEN**** 11972


REQUIRED FORMAT:

NAME**** BILLREF DISPATCH DATE SL.NO
GURG**** 5241 *****11/01/2016 ****11914
GOA *****4394*****11/01/2016 ****11900
GOA *****4368 *****11/01/2016 ****11900
GOA *****4367 *****11/01/2016 ****11900
GURG*****4328**** 11/01/2016 ****11905
GURG*****4327**** 11/01/2016 ****11905
MUMB*****4305****11/01/2016 ****11907
MUMB*****4304****11/01/2016 ****11907
MUMB*****4303****11/01/2016 ****11907
MUMB*****4302****11/01/2016 ****11907
JALA ******4301*** 11/01/2016 ****11899
JALA ******4300*** 11/01/2016 ****11899
JALA ******4299*** 11/01/2016 ****11899
JALA ******4294*** 11/01/2016 ****11899
GOA ******4212*** 11/01/2016 ****11913

It's really unclear what you want, and there appears to be more data in your required format sheet than in your source data. How is it possible to know the BILL REF?

Thank you so much for your reply Mr.Glenn Kennedy and brokenbiscuits.

brokenbiscuits:

Working Process.

* Our company is running all over the India.Daily dispatch's prepared on Invoice Number and Date against of corresponding to the Branch.

But dispatch annexure prepared based on branch.


* First I have apply the filter mode to select the blanks from column C.Here after I have choose the branch and select the Invoice Number against to update

the details column D.its based on Dispatch Annexure. This is our routine work.


*Do not find unique number in my data.so that I have enter to manually Column C and D.Its based on to update the details form rest of column H and I against corresponding to the Branch.

* Some time single branch against multiple dispatches made its happens based on Invoice Number against of dispatch's.Do not fix for the delimit of invoices.

its means some times single invoices are covered 50 Boxes,Some times a bunch of Invoices are covered 50 Boxes corresponding to the Branch.

Glenn Kennedy

Data short an ascending order.I had a lot of records in excel more than 40000 to 50000 in single month.data sort an ascending to face very difficult.

Here with file attached please help me.its very difficult to face manually enter if your suggestion bring to help me a lot and reduce our manual work.

please ref both sheets Date and Name ranges against return sl.no.

Still really struggling to make sense of this.

Are you just wanting to auto-populate column D? Even then, I'm not sure this will be possible as there are some SLs for the same date with the same name - 11905 and 11914 for instance - how would you know which SL was correct?

thank you so much to consider my query Mr.brokenbiscuits .

some time two are three dispatch made in single day against one branch. Each and every dispatch i have allocate serial number against branch and date.

11905 1 st dispatch in the morning and send dispatch 11914 evening corresponding to the range GURG against date 11/01/2016.

Thanks for clarifying that slightly - but as far as I can tell there's still no way to know which SL no you want to automatically populate in column D without further information in the DISPATCH ANNEXURE sheet.

please give some time i have change my required format and try to better explanation.

thansk you so much for your reply newdoverman sir.

i don't have a system my mobile not supported for Excel.i have check the file morning.here after please give me suggestion.