returning multiple values

[Lyn]

Hello Chandoo people!

I love your site but could not figure this one out without help. The formula on the priority setter tab in cell C2 returns the value one cell below the desired cell in the list tab. What I am trying to do is select all of the unique "formulas" associated with each project number on the list tab. I know my formula does not eliminate duplicate "formulas" yet but already I can;t get the big part of the formula working. In the real file there are 2000 entries so I do not want to do this by hand. I got it to work on the Mickey Mouse sheet also attached.

Lyn

 

[Luke M]

Hi Lyn, and welcome to the forum!

In C2 of the priority setting sheet, array formula could be:
=IFERROR(INDEX(List!$H:$H,(1/MIN(IF((List!$D$2:$D$25=$B2)*(ISNA(MATCH(List!$H$2:$H$25,$B2:B2,0))),ROW(List!$D$2:$D$25))))^-1),"")

This will display the unique formulas that match the project number. Copy to the right as far as would ever be needed. Unused cells will display blank.

 

[Lyn]

Hi Lyn, and welcome to the forum!

In C2 of the priority setting sheet, array formula could be:
=IFERROR(INDEX(List!$H:$H,(1/MIN(IF((List!$D$2:$D$25=$B2)*(ISNA(MATCH(List!$H$2:$H$25,$B2:B2,0))),ROW(List!$D$2:$D$25))))^-1),"")

This will display the unique formulas that match the project number. Copy to the right as far as would ever be needed. Unused cells will display blank.

Than k you. I see that it works in cell C2 but I have to figure out how to copy it to the rest of the table and keep it working. You used some formulas I am unfamiliar with so I'm off to learn what they do and why your formula works! ANother fun day with excel!!
Lyn

 

[Lyn]

Never mind it works in the sample sheet I sent you now - I forgot CSE. I still have to learn what you did so that I can get it to work in my real worksheet. Thanks Again Luke!!

 

[John Jairo V]

Hi @Lyn (and @Luke M)

Another approach could be (without CSE):
=IFERROR(INDEX(List!$H$2:$H$25,MATCH(,INDEX(COUNTIF($B2:B2,List!$H$2:$H$25)+(List!$D$2:$D$25<>$B2),),)),"")

Blessings!

 

[Lyn]

Interestingly, I was able to get Luke's formula to work in one cell (equiv of C2) in my real sheet. However when I increased the array size from 25 to 2500 it returned a blank suggesting an error (iferror part of formula) I changed it to 250 and again it worked. My actual List! is 1642 rows long so I chnage to the length array and it again was not working.

And John Thank you. I am new to CSE (learned about them yesterday) so your approach may be simpler for me to understand and execute. Thank you for an additional solution!

 

[Luke M]

Hi Lyn,

To increase size, formula becomes:
=IFERROR(INDEX(List!$H:$H,(1/MIN(IF((List!$D$2:$D$2500=$B2)*(ISNA(MATCH(List!$H$2:$H$2500,$B2:B2,0))),ROW(List!$D$2:$D$2500))))^-1),"")

"quick" explanation:
The IF functions builds 2 true/false arrays. First array is D2:D2500 equals the value in B2. Second array is whether value in H2:H2500 has already been listed in your formulas (the growing range of $B2:B2). Multiply these arrays together to make 1 single array of 1/0's. Anywhere there's a 1, the IF function will evaluate as True. The True argument for our IF function is the ROW number of the cell that met our condition. The False conditions will get the word "False"

Now that we have the ROW numbers, we take the smallest (MIN) value form this array, and throw it into the INDEX function. INDEX lets you give an array, and then asks what position in that array you want. So, if I says array is H:H, and get position 10, it will return H10.

One last trick. If the IF function returns an array of all Trues, then the MIN function returns 0. However, the INDEX function can accept 0 as an argument, and will return first cell in array. I don't want this to happen, as I want to display a blank if there are no True conditions. So, we do a math trick. If you look at this equation:
(1/X)^-1
This says to divide 1 by x, and then repeat. If you're familiar with the term, you're taking the reciprocal of a value twice. The trick here, is that for any value other than 0, this equation will evaluate to X. If it's 0, then you get a DIV/0 error. Thus, I'm purposely creating an error, so that my IFERROR function can trap it and give me the "" that I want.

 

[Lyn]

John Jairo V, Your formula is working for me. Thank you. I do not understand what it is doing really. The formulas are ones I have used before. What I do not understand are the isolated commas, the comma preceding index after the open parenthesis, and what is the plus sign doing there (why are we adding these two values).Can you explain? I tried disecting the formula and ended up with a lot of parts but not how they all fit together and why.

 

[Lyn]

So I manually went through each step and I see that the match returns a #N/A which is trapped by the iferror and I am out of play.

 

[Luke M]

I'll take a crack at John's formula.

=IFERROR(INDEX(List!$H$2:$H$25,MATCH(,INDEX(COUNTIF($B2:B2,List!$H$2:$H$25)+(List!$D$2:$D$25<>$B2),),)),"")

The two blue portions are each going to build a type of true/false array. The first one, the COUNTIF, says to check teh growing range of $B2:B2 for the values in H2:H25. If not found, this will be 0 (important). If it is found, will be any number > 0. The other true/false array is checking that D2:D25 <> B2. So, a False value (aka 0), will inidicate that D2:D25 does equal B2, and would be something we want.
Because of this construction, when you add the two arrays together, anywhere there's a 0 would be a row that we want, while everything else is something you want to ignore (because it's already been listed, or it doesn't match B2).

Now, we get some confusion due to the seemingly extra commas. In a formula, if you don't give an argument, XL treats it as 0. Let's fill in some 0's to see the formula the way XL sees it.
=IFERROR(INDEX(List!$H$2:$H$25,MATCH(0,INDEX(COUNTIF($B2:B2,List!$H$2:$H$25)+(List!$D$2:$D$25<>$B2),0),0)),"")

First, the red 0. As I mentioned in my explanation, if INDEX gets a 0 for it's row argument, it will return the whole array. In John's formula, this is what we want. We just used INDEX to have XL naturally create an array, w/o having to do CSE.
Next, the MATCH function takes that array, and is going to look for something. The lookup value is the green 0. Remember how I said the new array has a 0 whereever there's a row we want? This is where it comes into play. The MATCH function is going to look for that 0. The blue 0 says that we want an exact match in the MATCH function. As you found, if the MATCH function doesn't find a 0, it's going to throw the N/A error.

So, our MATCH function has returned a single value, saying which position (if any) within H2:H25 met our criteria. The INDEX function then takes this single input, uses it as the row arguement, and gives us the desired output. If the MATCH function had thrown the N/A error, then the INDEX will also error, and the IFERROR function takes over.

 

[Lyn]

Thank you, Some of that I was able to get on my own but I appreciate the thorough explanation. I'm getting better at eXcel all the time. Are you suggesting that if an argument is missing, like the 0 at the end of MATCH for an exact match, eXcel assumes it is 0, or exact match? Can one type the values (0) in or is it best/ required to keep the apparent "extra" commas by not putting anything there.

 

[John Jairo V]

Excellent explanation of formulae @Luke M! Cheers and Congrats!

 

[Luke M]

Thank you, Some of that I was able to get on my own but I appreciate the thorough explanation. I'm getting better at eXcel all the time. Are you suggesting that if an argument is missing, like the 0 at the end of MATCH for an exact match, eXcel assumes it is 0, or exact match? Can one type the values (0) in or is it best/ required to keep the apparent "extra" commas by not putting anything there.

Answering carefully here...if you give a spot for the argument, but don't put anything in that spot, then yes, XL interprets that as 0. The slight difference is apparent with functions that have optional arguments. Let's look at the MATCH function again

The last argument, match_type, is in square brackets to indicate that it's an optional argument. If you look at the help documentation, we see that if you don't give the argument, XL uses a 1


So, there's a difference between this:
=MATCH("Chandoo",A2:A100)
and this:
=MATCH("Chandoo",A2:A100,)

In the first, we didn't give a spot for the 3rd argument, so XL used the default of 1. In the latter, we gave a spot, but it's blank, so XL interprets that blank as 0.
Again, slight difference in apperance, but makes a big difference in how function operates.

Which way is better? FUnctionally, it makes no difference, and it comes down to personal preference. All 3 of these formulas are exactly the same functionally:
=VLOOKUP(A2,A:D,4,FALSE)
=VLOOKUP(A2,A:D,4,0)
=VLOOKUP(A2,A:D,4,)

My suggestion is to go with whichever one is easiest for you (and potentially future editors of your formulas) to understand. If it's a one off formula for myself, I tend to be lazy and save the keystrokes. If I'm writing a formula that others will see and need to understand, I tend to put the 0. But again, either way is acceptable.