Bit of a Tricky One

boojooo

New Member
Joined
Nov 15, 2012
Messages
33
That's not a bad idea, but in this instance I wouldn't want to see an error returned to let me know that something went wrong. If it returns a blank, that tells me nothing has been inputted in the cell that the formula is drawing from. I'm using these as order entry sheets so instead of a bunch of N/A's we want to see just empty cells. I do have a more complicated one if you're still available though?
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,653
Office Version
365
Platform
Windows
but in this instance I wouldn't want to see an error returned to let me know that something went wrong. If it returns a blank, that tells me nothing has been inputted in the cell that the formula is drawing from. I'm using these as order entry sheets so instead of a bunch of N/A's we want to see just empty cells.
That is precisely what the last formula I posted would do.
Your original formula might return N/As, if your conditions is met, but it doesn't find anything when looking up the value.

I do have a more complicated one if you're still available though?
If it is a new question that is not dependent upon knowing the answer and discussion around this one, it is best to post it to a new question. That way others will see it as a new unanswered question, in case I am not available or able to help. And there are people on this board smarter than me!
 
Last edited:

boojooo

New Member
Joined
Nov 15, 2012
Messages
33
Interesting, I was getting back zero's (0) is some cells when the formula was working correctly, but for the life of me couldn't figure out why. Let me input that and see if that clears it up.

My next one is somewhat tied to this one, but would now include four cells (A2-D2) and include headers (A1-D1). Basically, I need the formula to return the header value, but only from the cell below it that contains data. So if B2 contained a value then it would return the value in B1.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,653
Office Version
365
Platform
Windows
My next one is somewhat tied to this one, but would now include four cells (A2-D2) and include headers (A1-D1). Basically, I need the formula to return the header value, but only from the cell below it that contains data. So if B2 contained a value then it would return the value in B1.
While having some similiarities, it sounds like it is an entirely different question that would have a very different solution, probably an INDEX/MATCH solution. So it is probably best to post that to a new thread. I have done some INDEX/MATCH solutions, but it is not "in my wheelhouse", and there are others on this board who are far more proficient at it than me.
 

boojooo

New Member
Joined
Nov 15, 2012
Messages
33
No worries, you've been a lot of help! I tried that IFERROR solution, it's working but it not's working on a similar formula. Any suggestions?

=IF(H3=8,(VLOOKUP(B3,'B:\Menu Database\[BeerDatabase1.xlsx]Sheet1'!$A$3:$AG$40000,33,FALSE)),"")

I put the IFERROR after the H3=8,
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,653
Office Version
365
Platform
Windows
The format you want to follow is this:
IFERROR(VLOOKUP(...),"")
Basically, the way that it works is it has two arguments.
First argument: the formula you are checking
Second argument: what to return if the formula in the first argument returns an error

So, it will either return the value returned by the formula in the first argument, or what you tell it to return in case of an error.

So, to update your other formula, you would make it look like:
Code:
[COLOR=#333333]=IF(H3=8,[/COLOR][COLOR=#ff0000]IFERROR[/COLOR][COLOR=#333333](VLOOKUP(B3,'B:\Menu Database\[BeerDatabase1.xlsx]Sheet1'!$A$3:$AG$40000,33,FALSE),[/COLOR][COLOR=#ff0000]""[/COLOR][COLOR=#333333]),"")[/COLOR]
What may make it a little confusing is that your original formula has an extra set of parentheses which are not necessary. Your original formula could have been written just like this:
Code:
[COLOR=#333333]=IF(H3=8,VLOOKUP(B3,'B:\Menu Database\[BeerDatabase1.xlsx]Sheet1'!$A$3:$AG$40000,33,FALSE),"")[/COLOR]
 

boojooo

New Member
Joined
Nov 15, 2012
Messages
33
Lol, you're right on the second one. I got a little do () happy.

The first equation worked perfectly! Man that was driving me nuts when the zero's kept appearing randomly. Thank you again for all your help!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,653
Office Version
365
Platform
Windows
You are most welcome!:)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,438
Messages
5,486,889
Members
407,569
Latest member
Huzz

This Week's Hot Topics

Top