Help

[RussChilds]

Hi Everyone,

I'm stuck and was wondering if anyone can help.

I have the students name in Column A. In Columns B, C & D is the subject choice. The student will only have one subject choice in columns B, C or D. In column "E" I want to record the subject picked. Example:


A B C D E
Student Name History Geography Latin Subject Picked

Student 1 Latin Latin
Student 2 History History
Student 3 History History

So bascially I want to pull the answer from B, C or D into just one column (E)

Hope that makes sense?
Many thanks,
Russ

 

[jmacleary]

Hi there.
This array formula will give you the first non-blank cell in columns B to D. It needs to be entered as an array formula, so once you have copied it into cell D2 (where you want your result), use Ctrl+Shift+Enter on your keyboard. Then drag down.

Formuls: =INDEX(B2:D2,MATCH(TRUE,INDEX((B2:D2<>0),0),0))

 

[RussChilds]

Perfect!! Amazing. Thank you jmacleary!!

 

[jmacleary]

You're very welcome. I should have pointed out that if there is no entry, you get a #N/A, and if there is more than 1 entry you only get the leftmost one.

 

[jtakw]

Hi,

If there's more than 1 subject picked, this will give the rightmost value, if none picked, returns "None" (Change this to "" for Blank or whatever you want), Normally entered:

Book1
	A	B	C	D	E
1	Name	History	Geography	Latin	Picked
2	Student 1			Latin	Latin
3	Student 2	History			History
4	Student 3	History			History
5	Student 4				None
6	Student 5	History	Geography		Geography
Sheet522

Cell Formulas
Range	Formula
E2	=IFERROR(LOOKUP("zzz",B2:D2),"None")

Formula copied down.