Forum Discussion
Solved
Vlookup with multiple returns fix?
Here is my current formula. =IF(VLOOKUP(B10,InvoicesMain[[Company]:[Notes]],5,FALSE)<>"Complete", "Yes", "No") In basic, its looking for a customer and then determines if the service request is ...
Does this do what you want?
=IF(COUNTIFS(InvoicesMain[Company],B10,InvoicesMain[Notes],"<>Complete"),"Yes","No")
Under Customer, the open request field is supposed to check if that particular customer has any open request/invoice. If that customer has an open request, then it will return yes, if they do not, then it returns no.
My current formula works well if there is only one service request open. However, if there is more then one that has been open before, then it only returns the first it finds. An example of this is the customer number 101021. It sees the first request and returns "No" because the first request it finds for that customer is complete, and doesn't look for the second request that is open where it should say "Yes".
Not that I've found a formula, but basically I want to say "If any of 'this' <> 'complete', then return yes.
"this" being the vlookup to reference the status cell in the invoice main page.
My current formula works well if there is only one service request open. However, if there is more then one that has been open before, then it only returns the first it finds. An example of this is the customer number 101021. It sees the first request and returns "No" because the first request it finds for that customer is complete, and doesn't look for the second request that is open where it should say "Yes".
Not that I've found a formula, but basically I want to say "If any of 'this' <> 'complete', then return yes.
"this" being the vlookup to reference the status cell in the invoice main page.
Does this do what you want?
=IF(COUNTIFS(InvoicesMain[Company],B10,InvoicesMain[Notes],"<>Complete"),"Yes","No")
- I got it to work. I had to do your formula, but input it one piece at a time for some reason rather than copy paste and change the values. Heres what I ended up with. Thank you very much for you help
=IF(COUNTIFS(InvoicesMain[Company], B9,InvoicesMain[Status], "<>Complete"),"Yes", "No")Great! Thanks for the feedback!
- I had to manipulate it a little bit but its pretty close. the <> isn't working properly and references the cells strangely. when I take it out, it acts just like the original formula I had