Using multiple cells in VLOOKUP function for lookup_value - possible?

Hello!

I have three consecutive columns (B,C,D) and cells in these columns are filled with values or are empty.
Column J should display some text based on whether values in cells in columns G,H and I have certain values and those values correspond to a certain condition.

For example, B2 = 343, C2 = 319, D2 = 200;
my function should find these values in another sheet, check if the status is "done" for each of them, and put in J13 = "closed".

For another example, some B6 = 342, C6 = 319, but D6 is empty now, therefore nothing should be displayed in cell J13.

My basic formula that I am trying to expand is:

Formula:

Please Login or Register  to view this content.

I tried this:

Formula:

Please Login or Register  to view this content.

but it is obviously incorrect as it doesn't check conditions for each cell, doesn't it?

Please, help me to find a smart solution, preferably based on mathematical approach.
Thank you very much!

As always, please attach a sample workbook.

See if this works for you:

=IF(COUNT(B2:D2)=SUMPRODUCT((B2:D2=sheet2!B2:B4)*(sheet2!C2:C4="done")),"Closed","")

Thank you for your help. I am still trying it, but I would like to ask if I correctly understand how the formula works:

COUNT(B2:D2) gives us the number of columns with numbers; if B2,C2 and D2 are filled with numbers, then COUNT(B2:D2) returns 3;

B2:D2=sheet2!B2:B4 returns TRUE, namely 1, if the number of columns B2:D2 equals the number of rows sheet2!B2:B4 (otherwise returns FALSE, which is 0); if so, then it can't work in my case - there will be other values in sheet2 B column, not only B2:B4, it is much more extended, and I need to find there values listed in sheet1 B2:D2, and then find if those have "done" status;

sheet2!C2:C4="done" also returns TRUE if values in all cells C2:C4 are equal "done";

thus SUMPRODUCT((B2:D2=sheet2!B2:B4)*(sheet2!C2:C4="done")) should return 1, if both conditions are true, or 0, if any is false;

I don't see how this is evaluated COUNT(B2:D2)=SUMPRODUCT((B2:D2=sheet2!B2:B4)*(sheet2!C2:C4="done")) ?

Also, what is very important, it seems that this formula accounts only for consecutive values in sheet2, but there might be other values in column B in sheet2, and I need to find in sheet 2 only those listed in B2:D2 in sheet1, and then find if those have "done" status in sheet2. Values might be mixed with other values.

What you really need to do is provide sample data that truly reflects the real data you wish to work with. I have provided a solution based on what you have asked and what has been provided by you. If this doesn't work for you in your real scenario, then redefine the requirement and provide more realistic sample data.

Unfortunately, I won't be around for much of the day. Hopefully someone else can advise once you've provided the fresh dataset.

I did provide a refreshed data.
Also, I am a bit surprised to hear what you say because I myself usually take into account a few possibilities when I look for a solution, and in this case I thought it is obvious that the data can never be so straightforwardly aligned and be in a consecutive list, that there is always a mixture, and I have explained that I need to find those values on sheet2 and only then check their status, and based upon that put value in cell E2 in sheet1.

... and in this case I thought it is obvious that the data can never be so straightforwardly aligned and be in a consecutive list, ...

Obvious to you, of course, but how could I have known without guessing? There is nothing in your original post that suggests that the sample data might not be truly representative. I could have made all sorts of assumptions, but that can be dangerous in itself, which is why we specifically ask for sample data to be representative of the real data involved.

... and I have explained that I need to find those values on sheet2 and only then check their status, and based upon that put value in cell E2 in sheet1 ...

Yes, and based on the sample data, that is what the formula I gave to you does.

Anyway, I have to go out now.

PS I see you edited your opening post AFTER I provided my solution, with a dataset that differs siginificantly, but you did not tell us that you had done so. I don't as a matter of course keep checking original posts for updates, in fact if you are changing the sample data, it is much better to provide the new file at the approriate point in the thread, otherwise it just gets confusing for everyone trying to help. It also makes it appear as if my original suggestion was completely wrong, which it was not based on the original data. If you sense a hint of frustration in what I am typing, then you are right.

I am sorry! I should have written a note about the edit. When I edited the post, I thought about putting a special EDIT word inside the text, but then I noticed the Reason of your edit box, and filled it. Yes, didn't take into account that people not necessarily check the original post. Sorry once again!

OK, so please now respond to John's query in post #7.

And, not for the first time, could I plead with you not to quote unnecessarily? Don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding (e.g. my last post to you). Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

I didn't require you to become mind readers, and I simply mentioned the fact that it doesn't work other circumstances, and to understand why I need to understand the formula. I asked specific questions about the formula because I never copy anything mindlessly, but come here to learn from you - you are professionals, and know much more& But my questions were simply ignored, and instead I got a few lines of what I do wrong when posting here.

I never copy anything mindlessly, ...

This is a good thing! However, you will learn more effectively if you do things in the way we have advised, instead of the way that you think is correct and is becoming the cause of considerable frustration. Believe it or not, we ARE trying to help you!

Many of us here are not professionals when it comes to Excel: both John and I are self-taught, and that has been over a period of many years through fora like this one and other means, including trial and error. I have been given formulae that it has taken me a significant time to fully understand, indeed I am able to offer help here using complex formulae that I cannot myself fully explain, in some cases, so your insistance on understanding every aspect of the formula, whilst perfectly admirable, is actually holding you back.

Once we have got to the bottom of what you are actually trying to do with this formula then we can tell you what it does, how it does it and what you need to do to adapt it.

What you can then do, in your own time, is practise adapting formulae bit by bit: the evaluate formula feature on the formula ribbon is partcularly useful for this.

Here's what it's doing:

=IF(COUNT(B2:D2)

COUNT works with numbers in an array - it counts non-blank cells that contain a number - here, there are 3.

=SUMPRODUCT((B2:D2=sheet2!B2:B4)*(sheet2!C2:C4="done")),

This section gets Excel to generate an array of TRUE and FALSE values which resolve to 1s and 0s - there are 3 matches in this array where one of the numbers in our count array matches a number in the lookup array that has "done" next to it.

"Closed","")

Excel returns "Closed" because there are the correct number of matches in the array. Where there are not, it will return blank ("").