Lookup and Concatenate

[nico67]

Hi, I am having difficulties to figure out a formula to look if a person in the column J, has a date in column G that is equal to column I, so if yes, bring the content of the columns B to F in column K, if no could be FALSE, zero, or anything else.
K6 for example would be:
C, F, I, L, O
I was trying INDEX with MATCH but it did not work.
Thanks very much for your help!

[NBVC]

perhaps in K2:

=IFERROR(INDEX($B$2:$B$4&","&$C$2:$C$4&","&$D$2:$D$4&","&$E$2:$E$4&","&$F$2:$F$4,MATCH(1,INDEX(($A$2:$A$4=J2)*($G$2:$G$4=$I$2),0),0)),"")

copied down?

[DannyJ]

Hi Nico,

Any chance you could have the two lists in the same order?

If so use the formula =IF(G4=I2,B4&" "&C4&" "&D4&" "&E4&" "&F4," ") and drag down. If you are starting from row 2 then you can just drag it up two rows as well.

Regards

Danny

[nico67]

this is great, thanks very much!!!

[Fotis1991]

@ NBVC

Nice work.

Can you pls, explain(if you have time for this) a little more how 1 works in the match function?

....MATCH(1,INDEX(($A$2:$A$4=J2)*($G$2:$G$4=$I$2),0),0)),"")

[NBVC]

This part:

INDEX(($A$2:$A$4=J2)*($G$2:$G$4=$I$2)

checks two different conditions (i.e. if a2:a4 equals J2 and if G2:G4 equals I2). These two conditions each return results of TRUE and FALSE arrays... when you multiply 2 arrays of TRUEs and FALSEs you get a resultant array of 1's and 0's (TRUE*TRUE equals 1 and other combos all equal 0). The MATCH then looks for the first (and hopefully only) 1 in that resultant array to determine where both conditions are true.

Hope that helps.

[Fotis1991]

.....The MATCH then looks for the first (and hopefully only) 1 in that resultant array to determine where both conditions are true.

That's nice. Also as i noticed, formula works great even if exist 2 or more names that the date(in the same row) in column G, is equal to I2.

Good lesson. Thank you for this!

[nico67]

Hi,
I learned that the same name (Column A) can have one repeated date (Column G) with different items on it (Columns B-F), therefore I kindly ask you if you could please help me to figure out how to perform a second lookup considering the same criteria used before.

To better illustrate, L6 in the attached file should have A,G,Y,J,N

Many Thanks

[NBVC]

Try, in K2:

Please Login or Register  to view this content.

this has to be confirmed with CTRL+SHIFT+ENTER not just ENTER you will see { } type brackets appear around the formula. And then copied down and to next column (or columns, if there could be more duplicate names).

[nico67]

Thanks very much, it worked great!

[nico67]

Just one more thing, I understand that the formula does work when you now the cell range. I am getting the data from an external source and I won't be able to define/to know the cell range in the formula. Would it be a way to set the formula to read any cell range?
Thanks

[NBVC]

Which cell range are we talking about?

[nico67]

B,C,D,E,F - The data (number of rows) I will be getting will vary, therefore the formula with a fixed range, for example B$2$:B$5$ will not work if there are more rows. Thanks

[NBVC]

Ok, we can create a Dynamic Named Range. This will allow the rows to fluctuate.

Go to Formulas tab, then click Define Name in the Define Names section.

Enter a name for the DNR, say ItemList

Then enter formula in the Refers to field: =Sheet1!$A$2:INDEX(Sheet1!$G:$G,COUNTA(Sheet1!$A:$A))

Click Ok.

Now change formula in K1 to:

Please Login or Register  to view this content.

confirmed with CTRL+SHIF+ENTER and copied down and to next column.

[nico67]

Thanks very much!!!!!