vlookup with if/and returning value error

[ChrisTag1]

I have attached a file (I think). In the worksheet called Primary Daily Data Entry, I am trying to return a value based on cell values in the worksheet called Downtime Entry.

Matching the date from one sheet to the next, I need to return the value from cell D4 (Downtime entry) to cell Q4(Primary Daily Data Entry), but only if cells I4 and E4 (Downtime Entry) match exactly. Otherwise return 0.

this is my formula
=VLOOKUP(C3,'Downtime Entry'!$A$1:$L$526,IF(AND('Downtime Entry'!I4="primary",'Downtime Entry'!E4="DT"),'Downtime Entry'!D4,0))

Any ideas why it is returning #value! error and how I can fix it?

Thanks much
Chris

[Sam Capricci]

what is it you are looking to return? First, the DT in your formula is "DT" but the DT in the downtime entry tab is "DT " so it has a space after it so not a match.
next, most often the if then part precedes the vlookup like this... =IF(AND('Downtime Entry'!I4="primary",'Downtime Entry'!E4="DT "),VLOOKUP(C3,'Downtime Entry'!$A$1:$L$526,4,0))
and finally, what are you looking to return? If it is the time value in column D then adjust the formula as I have adjusted it above.

[ChrisTag1]

Sambo Kid, thank you so much. That works and is returning what I wanted, (which is the value in column D). I am still very new to excel and am trying to teach myself with help from here and google. I assumed the lookup would come first in the formula, because it needs to know where to look, but your way makes better sense.

Again,
Thank you for this.
Chris

[Sam Capricci]

You're welcome. As you learn, if you are using a vlookup, the vlookup looks to the right and begins in the column you put first in the lookup portion of your formula (which you have correct).

VLOOKUP(C3,'Downtime Entry'!$A$1:$L$526,4,0)

Now the other part in bold red is overkill so to speak. If you are going over 4 columns you only need to go to $D$526 as everything past that point will be ignored by the formula.
This site will be a good resource for you to use.

Oh, and thank you for the rep!

[ChrisTag1]

Sambo kid,

Another quick question, this formula is returning False instead of 0. Is there somewhere in the formula that I need to add 0?

Thank you.

=IF(AND('Downtime Entry'!I4="primary",'Downtime Entry'!E4="DT",),VLOOKUP(C3,'Downtime Entry'!$A$1:$L$526,4,0))

I have tried adding a 0 after primary and DT, and also tried using "" instead of 0 at the end of the formula. Neither seems to be doing what I want. I need the cell to return 0 instead of false.

[Sam Capricci]

as I noted in post #2, your "DT" in quotes should be "DT " with a space at the end because your DT in the Downtime Entry tab has a space at the end of it.
Test it separately with ='Downtime Entry'!E4="DT"
that will return false. If you go to that cell (E4 in that tab) and click on the formula bar where you see DT you will see your cursor is not next to the T but it is one space away meaning that the DT in your dropdown selection has an extra space. Hope that helps.

[Sam Capricci]

now to return something other than false you might want to put an IFERROR around either sections of the formula or the whole formula, though a False is not an error.

an iferror can be either here =IF(AND('Downtime Entry'!I4="primary",'Downtime Entry'!E4="DT"),IFERROR(VLOOKUP(C3,'Downtime Entry'!$A$1:$L$526,4,0),0))

or here =IFERROR(IF(AND('Downtime Entry'!I4="primary",'Downtime Entry'!E4="DT"),VLOOKUP(C3,'Downtime Entry'!$A$1:$L$526,4,0)),0)
notice the location of it and the location of the parenthesis at the end differ.

But your issue is the space at the end of DT which is causing the false.

[ChrisTag1]

I have corrected the space in "DT" in my dropdown list and also in the formula, so everything matches. It is still returning false, regardless of whether there is an "iferror" placed in the formula or not. I will keep working at it, as I am sure there is something I am missing.

Thanks for your help

[Sam Capricci]

it worked ok in the first example you posted so if you want to post it again I can look at the reason.

[AliGW]

If this is yet to be solved, please remove the solved tag for now. Thanks.

[ChrisTag1]

This should be an updated copy.

Thank you

[Sam Capricci]

the reason you are getting FALSE in cell (primary daily data entry) Q4 is because your formula is predicated on two conditions being true (at the same time), that downtime entry I5 equals primary and downtime entry E4 equals DT. But in E5 of the downtime entry tab is SS so that makes the condition FALSE.
When you write embedded formulas the formula looks from left to right and in this case the your first two conditions return false because BOTH are NOT met. If instead of an AND you had an OR like this... =IF(OR('Downtime Entry'!I5="primary",'Downtime Entry'!E5="DT"),VLOOKUP(C4,'Downtime Entry'!$A$1:$L$526,4,0))
it would return a value. But not knowing the point of your formula I don't know what other conditions OR even if other conditions are necessary, for example do you even need the IF / THEN part?
But that is why you are getting the FALSE.

[ChrisTag1]

=--(IF(AND('Downtime Entry'!I4="primary",'Downtime Entry'!E4="HR"),VLOOKUP(C3,'Downtime Entry'!$A$1:$L$526,4,0)))

Sambo kid,
I got my co-worker to also look at this with me, and he was able to find a solution. The above formula is updated and works exactly how I want it to. It is returning a value of 0 instead of "false" in the columns where needed. Feel free to try it on the previous file attached. Neither of us know why it works yet, but with some research I am sure I will figure it out.

Thank you so much for all your help. I apologize if my questions weren't clear enough for you to understand exactly what it was I was looking for. I never would have gotten this far without your help on the original formula.

Cheers,
Chris

[Sam Capricci]

glad your coworker helped you.
FYI, looking at your dropdown selections in downtime entry for E4 prompted me to offer this one that gives you a variety for that part of the equation...
=IFERROR(IF(AND('Downtime Entry'!I4="primary",LOOKUP(1,-SEARCH({"DT","HR","SS","LF","RL"},'Downtime Entry'!E4))),VLOOKUP(C3,'Downtime Entry'!$A$1:$L$526,4,0)),0)
The lookup search functions allow you to have it return a value if I4 is primary and E4 is any one of those values in the dropdown.
For what it is worth.

[ChrisTag1]

Thank you for that. I will keep that in mind. My spreadsheet is far from finished, I think I have finished 10 of about 16 worksheets, all linking to each other in some way.
Which is why all the errors in the parts I uploaded.

Thanks again for everything.

Cheers,