Returning a value via Vlookup but only When a condition in another field is met

[Genes22]

Is there any way to run vlookup but to limit the formula based on the condition in another field.
My vlookup is the standard formula,

=VLOOKUP(B2,'LocateIT Imported JB Stock File'!C:E,3,FALSE)

The below image is of LocteIT imported JB stock file.

but I only want to return the vlookup value if the status = 100 ie not if the status is 202.
Can this be done in Excel?

Normally, I would convert the below table to a pivot, filter on only status 100 and then refer to this pivot table to get the vlookup value.

I assume there must be a better way to do this.
Using Office 365 in Windows environment.

TIA

 

[RoryA]

You can use filter on the lookup table:

=VLOOKUP(B2,FILTER('LocateIT Imported JB Stock File'!C:E,'LocateIT Imported JB Stock File'!D:D=100),3,FALSE)

or skip the lookup altogether:

=FILTER('LocateIT Imported JB Stock File'!E:E,('LocateIT Imported JB Stock File'!D:D=100)*('LocateIT Imported JB Stock File'!C:C=B2))

 

[Genes22]

You can use filter on the lookup table:

=VLOOKUP(B2,FILTER('LocateIT Imported JB Stock File'!C:E,'LocateIT Imported JB Stock File'!D:D=100),3,FALSE)

or skip the lookup altogether:

=FILTER('LocateIT Imported JB Stock File'!E:E,('LocateIT Imported JB Stock File'!D:D=100)*('LocateIT Imported JB Stock File'!C:C=B2))

Thanks Rory, that worked a treat!