Excel formula needed to return column letter

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jha900

Return column containing number 1
I have an excel file with 5 columns A1:E1 and 100 rows. Each row has numbers 1 to 5.
I need a formula in F1 that will return the column that has the number 1 in it. So in the example below, I need it to return A for row 1, E for row 2 and D for row 3.

A B C D E
1 5 4 3 2
5 4 3 2 1
2 5 4 1 3

Keebellah

Hans
Hi, welcome to the forum,
I've got just the vba code for you.
I use it all the time.
Unpack the zip file and insert the vba module in you VBA project,

The syntax is Ch2Rng(any number) and the result is the corresponding column letter

ie: Ch2Rng(65) = "A"

If the range is outside the Excel range it will return "---"

Have fun with it use it at will and if you have any questions ... just holler!

Attachments

• 742 bytes Views: 46

WendyM

Or if you'd rather use a formula, you can try this in the first row and then drag it down all 100 rows. Just as an FYI, it doesn't work if you have more than 26 columns, but should be fine for A:E.

=CHAR(64+(SUMPRODUCT((A1:E1=1)*(COLUMN(A1:E1)))))

fletch_1960

Hi jha900, its my first time on the forum.
If you type the following into cell F2 and copy it down, this should give you what your lookin for.
Hope this helps,

=MID("ABCDE",MATCH(1,C3:G3,0),1)

Zack Barresse

Hi, welcome to the board!

I went to post my formula, but it pales in comparison to Fletch's! That, is a thing of beauty.

Here is what I had, and I would still use Fletch's formula, even if only in lieu of eloquence...
Code:
``=LEFT(ADDRESS(ROW(),MATCH(1,A1:E1,0),4),1)``

WendyM

Awww, but what about me, Zack? I have SO many more parentheses. That IS the criteria for a good formula, right? Lots of parentheses?

jha900, I think the lesson here is that there are lots of ways to do it, but you should always use the least complex method you can find. Which may or may not be (ok, fine, it's not) the one with the most parentheses.

Zack Barresse

LMBO!! I knew there was a reason I liked you Wendy, your ubiquitous use of parenthesis. Touche.

Keebellah

Hans
... and then there's vba functions with two parenthesis () ???

fletch_1960

Thanks all. I was so worried about posting it, i didn't want to step on anyones toes. Anyway, thanks for the vote of confidence and your kind words. I dare say, we will cross paths again soon! Fletch

Keebellah

Hans
Those that have long toes should wear larger shoes.
We're all learning and new / different ideas are welcome, like the saying:

Minds are like parachutes, they only work when they're open.

Zack Barresse

That's why I love this board. I learn something new every day!

@Hans: And some of us need that reserve chute!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On