Lookup function, need help
 

Hello,

Example:

A B C D
1 Mark David Elise Ted
2 X P X X


what I want:
A formula to give me the names of the people(row 1) with a "X" in row 2

Can't seem to figure it out, tried Lookup functions...

Thanks a lot,
Johan

do not know what you really want but try
=if(A2="X",A1&" ","")+if(B2="X",B1&" ","")+if(C2="X",C1&"
","")+if(D2="X",D1&" ","")+

"johanvdv" wrote:

Hello,

Example:

A B C D
1 Mark David Elise Ted
2 X P X X


what I want:
A formula to give me the names of the people(row 1) with a "X" in row 2

Can't seem to figure it out, tried Lookup functions...

Thanks a lot,
Johan

it works if I put a CONCATENATE around it but:

- I can only concatenate 5 strings
- I have A LOT of columns, it would become a VERY large formula

Is't there a shorter, better way ?

Hi Johan,

You don't mention in what form you need the names (in a string, in a column,
etc.) but I would transpose my data into another sheet, then you can use a
simple autofilter to see rows with X in column B.

Post if this solution meets your needs and if you want additional help on
transposing and filtering!

Regards,
Stefi

€žjohanvdv€� ezt Ã*rta:

it works if I put a CONCATENATE around it but:

- I can only concatenate 5 strings
- I have A LOT of columns, it would become a VERY large formula

Is't there a shorter, better way ?

another way to do it
use a helper row
(3?)
in A3 enter
=if(A2="X",A1&" ","")
copy across your data
somewhere else
=Concatinate(3:3)


"johanvdv" wrote:

it works if I put a CONCATENATE around it but:

- I can only concatenate 5 strings
- I have A LOT of columns, it would become a VERY large formula

Is't there a shorter, better way ?

How many columns do you have?
It will probably require a VBA solution;
Jim


"johanvdv" wrote in message
oups.com...
it works if I put a CONCATENATE around it but:

- I can only concatenate 5 strings
- I have A LOT of columns, it would become a VERY large formula

Is't there a shorter, better way ?

bj wrote...
do not know what you really want but try
=if(A2="X",A1&" ","")+if(B2="X",B1&" ","")
+if(C2="X",C1&" ","")+if(D2="X",D1&" ","")+
....

Always best to test. Using + as operator with guaranteed nonnumeric
string operands will always produce #VALUE! errors. Did you mean to put
& between all the IF calls?

Hi,

Try,

In A3 and copied down,

=IF(ROW(A1)-ROW($A$1)+1COUNTIF($A$2:$D$2,"x"),"",INDEX($A$1:$ D$1,SMALL(IF($A$2:$D$2="x",COLUMN($A$2:$D$2)),ROW( A1)-ROW($A$1)+1)))

Or if you want the results in one cell,

=SUBSTITUTE(aCONCAT(IF(A2:D2="x",A1:D1),","),",Fal se","")

Confirm with Ctrl+Shift+Enter.

ACONCAT is a UDF.


Code:
--------------------
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function
--------------------


HTH


--
Krishnakumar


------------------------------------------------------------------------
Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138
View this thread: http://www.excelforum.com/showthread...hreadid=465642