Filtering values by V-lookup based on multiple column values

prathap.krishnashetty69881 · 01/19/20

Hi, i'm trying to filter values without YES in Column2 of sheet Y by Vlookup from Column2, Column3 of sheet X where Column3 is tagged as YES. Here is the formula: =COLLECT([Column2]1:[Column2]8, {New Sheet Range 1}, @cell=Value<>Yes). I feel something wrong with my code or approach. Any help appreciated.

Paul Newcome · 02/24/20

HERE is a link to a published sheet that contains the solution for parsing down a column from a list on a different sheet. My initial JOIN/COLLECT in String1 is going to be different because I pulled the data based on different criteria. The rest of the sheet though, you should be able to replicate for your sheet. This will parse the first set of data down your first column. Then you would set up another Comma and String column for your next list column, duplicate for your third list, etc. etc. All you would need to do is adjust your JOIN/COLLECT to pull the appropriate data.

prathap.krishnashetty69881 · 01/22/20

I tried many ways, but unable to arrive for expected solution

Genevieve P. · 01/22/20

Hi @prathap.krishnashetty69881

I'm not sure I quite understand what you are looking to do. Are you looking to build a formula in your second sheet that says if Column 3 in the other sheet does not have yes, then indicate this in the current sheet, based on the content in the Primary Column?

If so, you would need to decide what value you want returned in this sheet, if it does not have a Yes in the other sheet. For example, maybe you want this second sheet to say "Not Approved", "No Content" or "No Yes"? Then you could build a cross-sheet reference that is an IF statement, using an INDEX(MATCH to match the content to this other sheet.

An INDEX(MATCH formula works like this:

=INDEX({Column that has the value you want returned}, MATCH([Value you’re looking to match]@row, {Column with the value you’re looking to match in the other sheet}))

Try this, with the IF statement:

=IF(INDEX({Column 3 in other sheet}, MATCH([Primary Column]@row, {Column 2 in other sheet})) = "Yes", "", "No Content")

This formula will look to see if the number in the Primary Column has a "Yes" next to it in the other sheet. If it does, then it will return a blank cell (indicated with this ""). If it does not say "Yes" in the other sheet, then the current sheet will say "No Content".

Here are some Help Center articles that may help: IF function / Index Function / Match Function / Cross Sheet References

Let me know if I've understood what you are looking to do, and if this works for you.

Cheers,

Genevieve

prathap.krishnashetty69881 · 01/27/20

Hi Gene, Thanks for the quick response. Actually i'm looking for some kind of vlookup values where the Column 2 in other sheet with values as Yes, should be filtered out in the Column2 of formula sheet. Bottomline, formula filled column should only have values without Yes matching to Column 2 in other sheet.

Paul Newcome · 01/27/20

If a report will not work, you will need to join all of the "Yes" values together on your new sheet and then parse that data out. In the sheet you want to display the data in, you will want to add in 2 columns. In my example, I will call them "String" and "Comma".

[Primary Column]1 (dragfill down):

=VALUE(LEFT(String@row, FIND(",", String@row) - 1))

String1:

=JOIN(COLLECT({Other Sheet Column 2}, {Other Sheet Column 3}, @cell <> "Yes"), ",") + ","

String2 (dragfill down):

=RIGHT(String$1, LEN(String$1) - Comma@row)

Comma1:

0 (manually entered number zero)

Comma2:

=LEN(LEFT(String1, FIND(",", String1)))

Comma3 (dragfill down):

=LEN(LEFT(String$1, FIND(",", String$1, Comma2 + 1)))

As you dragfill these formulas down, it will parse out your list leaving out anything that has a "Yes" in [Column3] of the other sheet.

prathap.krishnashetty69881 · 02/04/20

Hi Paul,

i am not sure if i understood the logic here. let me put it in a simpler way. to be easier, i have also reentered the requirement snapshots below.

As per the snapshot of Sheet1, under Column5 i have used below formula to list the non Yes values:

=IF(Delivered1 = "yes", "", ProductID1)

Now, i'm looking for some kind of vlookup formula in Sheet2 (second snapshot) where the columns Zone1, Zone2, Zone3, Zone4 should only have ProductID values as a list in reference to Column5 (from Sheet1) related to Column Zone values from Sheet1.

Appreciate your help.

Paul Newcome · 02/05/20

@prathap.krishnashetty69881

VLOOKUP will only pull one value.

You will need to join each of the values together that meets your criteria then parse it out. The solution I provided above is based on the screenshots in your original post where you had all "non-yes" values within the same column.

To be able to break it down further like you have in your second screenshot, [Primary Column]1 would become [Zone1]1. You would then need to include the additional zone range/criteria in the JOIN/COLLECT in String1.

This would give you the results for Zone 1. Then you would need to duplicate this solution for each of the additional zones.

prathap.krishnashetty69881 · 02/21/20

Hi Paul, can you help me with the second screenshot. when i try using the above string and comma formulas, i am getting unparseable. But, when i tried JOIN/COLLECT, it showing as below concatenated with comma. Is there a way to split this joint into different rows.

Paul Newcome · 02/21/20

Are you able to copy/paste both of the formulas from your sheet?

prathap.krishnashetty69881 · 02/21/20

=JOIN(COLLECT({Sheet1 Range 1}, {Sheet1 Range 2}, "Zone1"), ",")

this is the only formula, i was able to parse as per this snapshot

Paul Newcome · 02/21/20

What about the one that was giving you the error?

prathap.krishnashetty69881 · 02/21/20

=VALUE(LEFT([Zone1]1, FIND(",", [Zone1]1), - 1))

Paul Newcome · 02/21/20

That formula should not be giving you an #UNPARSEABLE error. If anything you should be getting an #INVALID VALUE error.

prathap.krishnashetty69881 · 02/21/20

you are right. help me, how to split this C2, JKU into

C2

JKU

Paul Newcome · 02/21/20

You should be able to follow the steps I posted above (and am re-posting here):

.

You will need to join all of the "Yes" values together on your new sheet and then parse that data out. In the sheet you want to display the data in, you will want to add in 2 columns. In my example, I will call them "String" and "Comma".