What is the best formula to use?

[catherine9910]

Hi everyone,

I have a project for work that I need help with. I have a macro pulling data from an external database, and I need to find a formula that will say if column a = blank, then return the next cell to the right of it.

At first I tried an IF THEN statement but got nowhere. So then, I figured the easiest would be a VLOOKUP, but it isn't working either and returns an #N/A. So, I added a screenshot of an example of the formula I was trying. What am I doing wrong or is there an easier way to do this?

The other thing is that there are spaces in between each row (that are not shown below) in my spreadsheet because the rows change in the data I'm pulling. What may have been in B3 today may be in B7 tomorrow. That is why I'm am needing to figure this out.

The formula should be saying 21, but it is saying #N/A

[#2]

@catherine9910 Assuming that the 10 in column B is number. If so, remover the quotation marks around the 10 in your formula, as it tries to match a text with a number and can't. Hence, #N/A.

Even better to replace the "10" in your formula with a reference to F3 and make the reference to the lookup range absolute, i.e. by putting $ signs in front of each column and row reference. Or create a named range or structured table of your data. Sorry, for offering all the different options, but there are many way to tackle things in Excel.

[#3]

No, I like that you have given me multiple options to try that way I can find the best one for my project. Thank you.

[#4]

remove quotations in 10 as this is number, then it should work