Specific result from Vlookup

[robinmech]

Hi,

I,m trying to vlookup one cell value with the other. And I got the result also. But I want only one particular result if the cell values are matching. Rest results I want to keep blank even if its matching.

For example I have a list of drawing no's in first sheet and second sheet have the same drawing no's with the status. For eg: approved, Rejected, commented etc. So If drawing no's are matching then I need the only "approved" result in first sheet rest all matching results should be left blank.

Please help.

Thanks in advance

 

[Aladin Akyurek]

Now it's working perfectly. Thank you

You are welcome. Looks like we have errand spaces around the entries in column 7 of REG, hence TRIM.

 

[robinmech]

You are welcome. Looks like we have errand spaces around the entries in column 7 of REG, hence TRIM.

Thank you once gain.

Now I'm facing one more issue with the result. When I'm using pivot table, its counting the non blank cells which contain "approved" in the formula. How can I resolve this.

 

[Aladin Akyurek]

Thank you once gain.

You are welcome.

Now I'm facing one more issue with the result. When I'm using pivot table, its counting the non blank cells which contain "approved" in the formula. How can I resolve this.

What is the formula? And what is your intent: not count the occurrences of "approve"?

 

[robinmech]

You are welcome.

What is the formula? And what is your intent: not count the occurrences of "approve"?

Sorry for the delay in reply.

I used the formula like this.

=iferror(if(trim(vlookup(cell,name,col,0))=”Approved”,”Approved”,””),””)

And I'm getting the result also..l

But when I'm trying to count the occurrence of approved; getting the result form the blank cells also. because its counting the test from the formula also.

my formula is COUNTIF(range,"Approved")

 

[Aladin Akyurek]

COUNTIF(range,"Approved")

won't count any blank.

Let's analyze: You used

=iferror(if(trim(vlookup(cell,name,col,0))=”Approved”,”Approved”,””),””)

then:

=COUNTIF(INDEX(name,0,col),"Approved")

must tell us how many times approved occurs.