Find Voucher no

[Shabbo]

Dear Sir,

Please find attached my excel workbook sheet1 I have date vehicle no and amount and sheet2 I have my data.

I wanted to find V_no6 No from column “O”, sheet2 based on date and vehicle no in sheet 1 from sheet2.

 

[shrivallabha]

Vehicle numbers are not separate in first sheet and if the pattern is not consistent then it can cause problems. I have assumed them to be always separated by dash (-) as per sample file.

To separate vehicle number in cell F3 I have put:
=TRIM(RIGHT(SUBSTITUTE(C3,"-",REPT(" ",99)),99))

Then using this value, LOOKUP is used to retrieve data from Sheet2
=LOOKUP(2,SEARCH(F3&B3,Sheet2!$I$2:$I$25&Sheet2!$D$2:$D$25,1),Sheet2!$O$2:$O$25)

File is attached for clarity.

 

[Shabbo]

Vehicle numbers are not separate in first sheet and if the pattern is not consistent then it can cause problems. I have assumed them to be always separated by dash (-) as per sample file.

To separate vehicle number in cell F3 I have put:
=TRIM(RIGHT(SUBSTITUTE(C3,"-",REPT(" ",99)),99))

Then using this value, LOOKUP is used to retrieve data from Sheet2
=LOOKUP(2,SEARCH(F3&B3,Sheet2!$I$2:$I$25&Sheet2!$D$2:$D$25,1),Sheet2!$O$2:$O$25)

File is attached for clarity.

Dear Sir,
Can we combine both formulas together in one column.

 

[shrivallabha]

Dear Sir,
Can we combine both formulas together in one column.

Yes, just replace F3 with first formula like below and you will have a single formula:
=LOOKUP(2,SEARCH(TRIM(RIGHT(SUBSTITUTE(C3,"-",REPT(" ",99)),99))
&B3,Sheet2!$I$2:$I$25&Sheet2!$D$2:$D$25,1),Sheet2!$O$2:$O$25)

I thought seeing what vehicle number was getting evaluated would help troubleshooting.