array if problem

[MartinL]

	A	B	C	D	E
1	Type	Black	White	Grey	ANSWER
2	single	1 x 5m			Black
3	multi	1 x 5ml	1 x 5ml	1 x 5 ml	multi
4	single			1 x ml	Grey
5	twin		2 x 5ml		White

<tbody>
</tbody>

I need to find the answer to E2:E5 by

If type = multi then Answer =multi
else ANSWER is the value of B2:D2 where the Active cell row is not blank

Hope this makes sense.

 

[adam087]

Does this work for you?

Excel 2010
	A	B	C	D	E
1	Type	Black	White	Grey	Answer
2	single	1 x 5m			Black
3	multi	1 x 5ml	1 x 5ml	1 x 5 ml	multi
4	single			1 x ml	Grey
5	twin		2 x 5ml		White
Sheet1

Cell Formulas
Range	Formula
E2	=IF(A2="Multi", A2, INDEX($B$1:$D$1, MATCH("*", B2:D2, 0)))

/AJ

 

[MartinL]

Holy cow was that quick

 

[adam087]

Happy to help. Thanks for the feedback.

/AJ

 

[MartinL]

If you don't mind I'd like to add to this question
In column F
Sum the 1st digits in each row so
F2 = 1
F3 = 3
F4 = 1
F5 = 2

 

[RoryA]

=SUM(IFERROR(LEFT(B2:D2,1)+0,0))
array entered with Ctrl+Shift+Enter

 

[MartinL]

Ohhh so close I left the IFERROR out thanks Rory