Even there is value below first row V LOOKUP Returns 0

Question

I have two different excel sheets. In one excel sheet I have only "Part number" in another one I have Part number and Plant. I am trying to get the Plant details in first excel sheet. Below are the screen shot for your reference.

Primary

Secondary

Expected result

I know the first row is zero hence I am getting zero in all the rows. Is there a way, If the vlookup finds a matching value that has a blank cell associated with it, look down the list until you find that matching value that has something in the cell next to it?? I tried the below function without any success.

=VLOOKUP(A2,IF(ISBLANK([Book2.xlsx]Sheet1!$BL:$BL),0,[Book2.xlsx]Sheet1!$A:$BL,29,0)

Any help in solving this issue will be deeply appreciated!

Answer 1

Assuming the following data from A1 in Sheet 1 and data for Material is grouped, but not necessarily sorted:

Material Part   
166 
166 
166 
166     XYZ
166 
166 
167 
167 
167     ABC
167 
167 
167 
167 
167 

to get the following result in Sheet2 from A1:

Material    Part No.
167         ABC
166         XYZ

Formula in B2:

=INDEX(OFFSET(Sheet1!$A$2,MATCH(A2,Sheet1!A:A,0)-2,1,COUNTIF(Sheet1!A:A,A2)),MATCH(FALSE,ISBLANK(OFFSET(Sheet1!$A$2,MATCH(A2,Sheet1!A:A,0)-2,1,COUNTIF(Sheet1!A:A,A2))),0))

Note above is a array formula to be enter with ctrl + shift + enter

Answer 2

Try,

=INDEX(B:B, AGGREGATE(15, 6, ROW($2:$15)/((A$2:A$15=E2)*(B$2:B$15<>"")), 1))
'another workbook's columns A and AC
=INDEX([Book2.xlsx]Sheet1!AC:AC, AGGREGATE(15, 6, ROW($2:$15)/(([Book2.xlsx]Sheet1!A$2:A$15=a2)*([Book2.xlsx]Sheet1!AC$2:AC$15<>"")), 1))

Sample¹:

¹ _{Thanks to Nitesh Halai for supplying sample data that did not have to be retyped from an image.}