Vlookup returning a blank cell

[dhally]

Hello;
I have viewed numerous threads regarding: Vlookup returning a blank cell if is lookup returns #NA.

Yet, when I use the the accepted formula format given on many different websites I get a return of a number zero when I really want a return of a blank cell. Here is my original Vlookup formula:
=VLOOKUP(A300,'VLOOKUP_FOR_BLANK_RANTALA_Request For SAP Upload Form.xlsx'!LookupContainer,12,FALSE)

Here is the formula I had hoped would return a blank cell:
=IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2))

Any help would be very appreciated. Thank you in advance
dh

 

[dhally]

Let me try what you just posted: =IFERROR(T(VLOOKUP(...)),"")

 

[T. Valko]

Let me try what you just posted: =IFERROR(T(VLOOKUP(...)),"")

If this is your data...

Book1
	A	B
1	Name	Region
2	Name1	North
3	Name2	East
4	Name3	South
5	Name4
6	Name5	West
7	Name6	East
8	Name7	South
9	Name8	North
10	Name9	North
Sheet2

=IFERROR(T(VLOOKUP("name4",A1:B10,2,0)),"")

Returns blank

=IFERROR(T(VLOOKUP("name100",A1:B10,2,0)),"")

Returns blank

=IFERROR(T(VLOOKUP("name3",A1:B10,2,0)),"")

Returns South

 

[dhally]

T. Valko,

This formula:
=IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",IF(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)=0,"",VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)))

returned a blank cell which was what I was looking for. It seems I left out the middle IF formula syntax as underlined.

Thank you so much!
dh

 

[T. Valko]

T. Valko,

This formula:
=IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",IF(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)=0,"",VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)))

returned a blank cell which was what I was looking for. It seems I left out the middle IF formula syntax as underlined.

Thank you so much!
dh

What type of data is that formula returning? Is it text or is it numeric or could it be both?

 

[dhally]

This formula: =IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",IF(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)=0,"",VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)))

returns a blank cell. I tested the cell (k301) on by typing the value "test" into it and it returned the value "test" in the cell containing the formula. When I clear the text cell "test" (k301) the cell containing the formula goes blank which is good in my case. I hope this makes sense.

 

[T. Valko]

This formula: =IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",IF(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)=0,"",VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)))

returns a blank cell. I tested the cell (k301) on by typing the value "test" into it and it returned the value "test" in the cell containing the formula. When I clear the text cell "test" (k301) the cell containing the formula goes blank which is good in my case. I hope this makes sense.

Ok, if you're using Excel 2007 you can shorten that formula significantly and make it more robust at the same time.

=IFERROR(T(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2,0)),"")

 

[dhally]

Yes...the current formula is quite long. Can I ask...what is the "T" value bold and underlined in the formula represent?

=IFERROR(T(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2,0)),"")

 

[T. Valko]

Yes...the current formula is quite long. Can I ask...what is the "T" value bold and underlined in the formula represent?

=IFERROR(T(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2,0)),"")

T stands for TEXT.

If the referenced value is TEXT then T(...) returns that value otherwise returns blank "" (unless it's an error then it returns the error).

Book1
	A	B
1	text	text
2	x	x
3	_	_
4	10	_
5	#N/A	#N/A
Sheet2

Formula entered in B1 and copied down:

=T(A1)

 

[Jonmo1]

This formula: =IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",IF(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)=0,"",VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)))

OK, I'm very confused...which is not unusual

You have a 1 Row array in the vlookup.
Rendering the Vlookup pointless, you could just do this
=IF('VLOOKUP CONTAINER'!K301<>A301,"",IF('VLOOKUP CONTAINER'!L301="","",'VLOOKUP CONTAINER'!L301))

But I guess you are just "simplifying" the formula for our reading benefit...


Also, I think you are completely missunderstanding the main question T. Valko is asking...


We know sometimes Vlookup returns #N/A because it doesn't find the match
We know sometimes Vlookup returns 0 because the resulting cell is blank or 0.

Now, ignoring those occasional results, when the vlookup actually does what you expect it to do...
What type of values do you expect the vlookup to return? Text or Number or could be either?

In other words, forget about your vlookup for a moment,
what type of data is in Column L, text/number/either ?

 

[dhally]

It works perfect.

I can't tell you how many threads I viewed on this particular "vlookup..return blank cell value". I Saw plenty of the IFERROR(Vlookup...) but
and none of them showed the "T" (text) portion of the formula syntax as yours which of course did the trick.

This is why I didn't sign up to post on any other sites except Mr. Excels question board!

Very happy and many grateful thank you's,
dh