# Help

#### RussChilds

##### Active Member
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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))

Perfect!! Amazing. Thank you 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.

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
ABCDE
1NameHistoryGeographyLatinPicked
2Student 1LatinLatin
3Student 2HistoryHistory
4Student 3HistoryHistory
5Student 4None
6Student 5HistoryGeographyGeography
Sheet522
Cell Formulas
RangeFormula
E2=IFERROR(LOOKUP("zzz",B2:D2),"None")

Formula copied down.

Replies
6
Views
220
Replies
0
Views
127
Replies
2
Views
214
Replies
4
Views
326
Replies
4
Views
256

1,206,834
Messages
6,075,134
Members
446,123
Latest member
junkyardforme

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back