Returning 2nd, 3rd, 4th value with vlookup?

[Jonsmith2212]

My company does inspections and the result of the inspection- blank cell or pass - is located on another work sheet.

Currently we are using a VLOOKUP to return the result of the inspection to the master worksheet. The formula looks like this: "=IFERROR(VLOOKUP(H1396,'Inspection Database'!H:X,17,FALSE),"")" Whereas the H1396 is the unique identifier (APN) of the property inspected. The issue is that a property can be inspected multiple times before it passes;therefore creating multiple rows with the unique identifier.

When it does finally pass the row in which it did will say "Pass" but it could have three or four other rows above it with a blank. The VLOOKUP returns the first match, so if a house has multiple inspections before a pass it will show a "0" on the main worksheet.
Is there a way to ignore the blanks and only return a value if it is a "Pass"?

[FlameRetired]

Hi Jonsmith2212 and welcome to the forum.

Since VLOOKUP only matches the first match it finds try INDEX / SMALL / IF

Since I don't have a layout of your data a made up a bare-bones data base of APN numbers and Status.

In B2 put the APN number in question.

Then array enter this formula in A2 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

This should give you an idea on how to obtain multiple criteria matches

See the attached.

[José Augusto]

Or you can get the rows of APN data using

Please Login or Register  to view this content.

and data using INDEX
I am using the example provided by @FlameRetired