IFERROR, VLOOKUP and another IF statement HELP!!

[Dimsum06]

Is there a way to add another criteria to this current formula? I want to add if this range/column has a certain word, in my case OUTSTANDING, to return BLANK"".
Current Formula: IFERROR(VLOOKUP(B9,'REPORT!A:C,3,FALSE),"")

Thank you!!!

 

[Z51]

Which range/column? Do you mean "if the result of the VLOOKUP function is "OUTSTANDING" then return "" instead? If so, use:

=IF(ISERROR(VLOOKUP(B9,REPORT!A:C,3,0)),"",IF(VLOOKUP(B9,REPORT!A:C,3,0)="OUTSTANDING","",VLOOKUP(B9,REPORT!A:C,3,0)))

 

[Dimsum06]

Which range/column? Do you mean "if the result of the VLOOKUP function is "OUTSTANDING" then return "" instead? If so, use:

=IF(ISERROR(VLOOKUP(B9,REPORT!A:C,3,0)),"",IF(VLOOKUP(B9,REPORT!A:C,3,0)="OUTSTANDING","",VLOOKUP(B9,REPORT!A:C,3,0)))

This is exactly what I needed. Thank you thank you thank you!!!!

 

[Peter_SSs]

Does just doing the lookup once like this also work?

=SUBSTITUTE(IFNA(VLOOKUP(B9,REPORT!A:C,3,0),""),"OUTSTANDING","")

If not, try

=SUBSTITUTE(IFERROR(VLOOKUP(B9,REPORT!A:C,3,0),""),"OUTSTANDING","")

 

[Dimsum06]

Does just doing the lookup once like this also work?

=SUBSTITUTE(IFNA(VLOOKUP(B9,REPORT!A:C,3,0),""),"OUTSTANDING","")

If not, try

=SUBSTITUTE(IFERROR(VLOOKUP(B9,REPORT!A:C,3,0),""),"OUTSTANDING","")

I will try and let you know!

 

[Dimsum06]

I will try and let you know!

Tried both and it did not work. But it's ok the other user assisted! Thanks for trying!

 

[Dimsum06]

Does just doing the lookup once like this also work?

=SUBSTITUTE(IFNA(VLOOKUP(B9,REPORT!A:C,3,0),""),"OUTSTANDING","")

If not, try

=SUBSTITUTE(IFERROR(VLOOKUP(B9,REPORT!A:C,3,0),""),"OUTSTANDING","")

try and let you know!

Tried both and it did not work. But it's ok the other user assisted! Thanks for trying!

 

[Peter_SSs]

Tried both and it did not work.

I'm interested in what way it (especially the second one) did not work? Here are four circumstances that I can think of and post 2 formula in column C (in second mini-sheet below) and my second formula in column D are returning the same results. What have I missed?

Dimsum06.xlsm
	A	B	C
1	a		#DIV/0!
2	c		OUTSTANDING
3	d		xx
REPORT

Dimsum06.xlsm
	B	C	D	E
9	a			Lookup finds a value but it is an error value
10	b			Lookup cannot find the lookup value (b) so an error is returned
11	c			Look result is OUTSTANDING
12	d	xx	xx	Lookup finds a non-OUTSTANDING value
Sheet1

Cell Formulas
Range	Formula
C9:C12	C9	=IF(ISERROR(VLOOKUP(B9,REPORT!A:C,3,0)),"",IF(VLOOKUP(B9,REPORT!A:C,3,0)="OUTSTANDING","",VLOOKUP(B9,REPORT!A:C,3,0)))
D9:D12	D9	=SUBSTITUTE(IFERROR(VLOOKUP(B9,REPORT!A:C,3,0),""),"OUTSTANDING","")

 

[Dimsum06]

I'm interested in what way it (especially the second one) did not work? Here are four circumstances that I can think of and post 2 formula in column C (in second mini-sheet below) and my second formula in column D are returning the same results. What have I missed?

Dimsum06.xlsm
	A	B	C
1	a		#DIV/0!
2	c		OUTSTANDING
3	d		xx
REPORT

Dimsum06.xlsm
	B	C	D	E
9	a			Lookup finds a value but it is an error value
10	b			Lookup cannot find the lookup value (b) so an error is returned
11	c			Look result is OUTSTANDING
12	d	xx	xx	Lookup finds a non-OUTSTANDING value
Sheet1

Cell Formulas
Range	Formula
C9:C12	C9	=IF(ISERROR(VLOOKUP(B9,REPORT!A:C,3,0)),"",IF(VLOOKUP(B9,REPORT!A:C,3,0)="OUTSTANDING","",VLOOKUP(B9,REPORT!A:C,3,0)))
D9:D12	D9	=SUBSTITUTE(IFERROR(VLOOKUP(B9,REPORT!A:C,3,0),""),"OUTSTANDING","")

2nd formula was still returning OUTSTANDING when I'd like it to return "".

 

[Peter_SSs]

2nd formula was still returning OUTSTANDING when I'd like it to return "".

That is not happening with the sample above and I cannot see how it is possible since the formula contains SUBSTITUTE(...,"OUTSTANDING","") ...
... unless your "OUTSTANDING" is not all upper case like is written in each case above?

=LET(v,IFERROR(VLOOKUP(B9,REPORT!A:C,3,0),""),IF(v="OUTSTANDING","",v))