If Then Concatenation

I have a worksheet that has data in cells A3 - J3. One value will be placed in one of the cells directly below A3-J3. Is there a way to look at the cells A4-J4 and determin what cell has the value and then preform a concatenation on that cell pluss the one directly above it.

I have included a worksheet as an example.

Thanks for any and All help.

-Lee

If the value inserted into row 4 is always 1 then:

=INDEX(A3:M3,MATCH(1,A4:M4,0))&" 1"

Else you will need to alter slightly... not much though.

Perhaps:

=LOOKUP(2,1/(A4:M4<>""),A3:M3&" "&A4:M4)

for any value or text in row 4
=INDEX(A3:M3,MATCH(0,INDEX(--(A4:M4=0),0),0))&" "&INDEX(A4:M4,MATCH(0,INDEX(--(A4:M4=0),0),0))
then again
the above is somewhat shorter !!

Martin, that's an interesting approach and not one I've seen before, however, it wouldn't work if the value entered in row 4 was 0 whereas the more "traditional" LOOKUP approach would cover all bases (and has less calls).

Assuming the values in row 4 are NOT results of formulae (ie blanks not nulls) then:

=IF(COUNTA(A4:M4),LOOKUP(2,1/(A4:M4<>""),A3:M3&" "&A4:M4),"")

If they are results of formulae and blanks are really Nulls you will need to change the approach to one of:

=IF(COUNTBLANK(A4:M4)<COLUMNS(A4:M4),LOOKUP(2,1/(A4:M4<>""),A3:M3&" "&A4:M4),"")

or

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(2,1/(A4:M4<>""),A3:M3&" "&A4:M4)))

donkeyote you can use MATCH(0,INDEX(--(A4:M4=""),0),0) instead
but as you say lookup is shorter

agreed, you could ... would you want to though ??

No complaints from me though I like seeing new approaches to stuff!