LookUp skipping empty cells and returning first entry

[Colin Smit]

Hi There

I have a problem where I am trying to do a VlookUp on a certain item (Commodity Code) and it must return other results relevant to that specific item. My problem is that there are empty cells on the returned results of the first entry for that item. VlookUp only looks at the first line for the item. I know that VlookUp must be changed to the functions using Match & Index. However even after a previous post on the same concern I still do not understand how to get it to work. See attached workbook.

I am using a few user forms to do entries of which only certain information is required based on the relevance. Thus empty cells will always be a part of the workbook. The lookup needs to overlook this.

Any help will be appreciated.

[JBeaucaire]

1) if it's always off by ONE row, meaning the match is to row2 then the data is in row3, then that's easy enough, we'll just +1 on the match.

2) Note you entered a number 2009045 on the LookupDetails sheet, but on the Data sheet column J is not numbers, it's text. Notice the little green markers in those cells? Excel has noticed this common problem too and is trying to warn you those numbers are not numbers, they are text strings.

Assuming the text in column J will always be text and we need coerce a match from these non-matching data types, we'll build that into the formula, too.

4) On LookupDetails, go down column B and remove the colons: from the end of the text, this way the values in those cells match exactly to the text across row 1 on the Data sheet.

5) Finally, put this formula in C5 and then copy down:

=INDEX(Data!$1:$1048576, MATCH($C$4&"", Data!$J:$J, 0)+1, MATCH($B5, Data!$1:$1, 0))

[Colin Smit]

1) if it's always off by ONE row, meaning the match is to row2 then the data is in row3, then that's easy enough, we'll just +1 on the match.

Thank you for the help thus far. It works provided that the match is in the second row.
This is however not the case as the results are staggered.

Can your code be altered so that it looks for the first entry made?

[samba_ravi]

Then TRY
=IF(ISBLANK(INDEX(Data!$1:$1048576, MATCH($C$4&"", Data!$J:$J, 0), MATCH(LEFT($B5,LEN(B5)-1), Data!$1:$1, 0))),INDEX(Data!$1:$1048576, MATCH($C$4&"", Data!$J:$J, 0)+1, MATCH(LEFT($B5,LEN(B5)-1), Data!$1:$1, 0)),INDEX(Data!$1:$1048576, MATCH($C$4&"", Data!$J:$J, 0), MATCH(LEFT($B5,LEN(B5)-1), Data!$1:$1, 0)))
and Change
Loose Piece Material
Pattern Movement (In/Out)
Delivery Note
as
Loose Piece Material:
Pattern Movement (In/Out):
Delivery Note:

[Colin Smit]

Then TRY
=IF(ISBLANK(INDEX(Data!$1:$1048576, MATCH($C$4&"", Data!$J:$J, 0), MATCH(LEFT($B5,LEN(B5)-1), Data!$1:$1, 0))),INDEX(Data!$1:$1048576, MATCH($C$4&"", Data!$J:$J, 0)+1, MATCH(LEFT($B5,LEN(B5)-1), Data!$1:$1, 0)),INDEX(Data!$1:$1048576, MATCH($C$4&"", Data!$J:$J, 0), MATCH(LEFT($B5,LEN(B5)-1), Data!$1:$1, 0)))
and Change
Loose Piece Material
Pattern Movement (In/Out)
Delivery Note
as
Loose Piece Material:
Pattern Movement (In/Out):
Delivery Note:

The formula works provided the data is in the same format. However when data is staggered the returned values are 0. See new workbook where I have placed extra data in and staggered the needed results all over on different rows.

[samba_ravi]

C5=IFERROR(INDEX(INDEX(Data!$A$1:$X$22,,MATCH(LEFT($B5,LEN($B5)-1),Data!$1:$1,0)),MATCH(0,INDEX(((Data!$J$1:$J$22<>$C$4&"")+(INDEX(Data!$A$1:$X$22,,MATCH(LEFT($B5,LEN($B5)-1),Data!$1:$1,0))=""))*10^10,0),0)),"")
Try this and copy towards down

[Colin Smit]

C5=IFERROR(INDEX(INDEX(Data!$A$1:$X$22,,MATCH(LEFT($B5,LEN($B5)-1),Data!$1:$1,0)),MATCH(0,INDEX(((Data!$J$1:$J$22<>$C$4&"")+(INDEX(Data!$A$1:$X$22,,MATCH(LEFT($B5,LEN($B5)-1),Data!$1:$1,0))=""))*10^10,0),0)),"")
Try this and copy towards down

Works 100% up until row 22. I changed the code to look at all the rows and now it works for the whole sheet.

Thank You for the help!!

[samba_ravi]

Glad to help you and thanks for your feedback

[Vikas_Gautam]

Try this if you can...

Please Login or Register  to view this content.