CODEWITHSUNDEEP

excelexcel-formula

lolozen

Reputation: 426

how to find a column letter from it's name or from a text in it?

I'm creating a sheets with type and month, named datasheet,what I'd like to do is a look up part:

I'd like to create a formula that gives me the type 3 of a chosen month, so if I give January it returns the type 3 of January

So I created a list from my month

and then I tried to create a formula on another sheet that used the reference of a cell to return me it's value

=INDIRECT("datasheet!"&CHR(COLUMN(datasheet!B1)+64)&4)

my question is how can I look for the column of a given column name or look for the first column in a row containing a specific text ?

Upvotes: 0

Views: 1072

Answers (2)

Pat Gooch

Reputation: 51

If you always want it to give you the type3 value, and not use the row description to search, you could use an HLOOKUP for this. https://support.microsoft.com/en-us/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f

=HLOOKUP(B1,datasheet!A1:M4,4)

Upvotes: 1

Scott Craner

Reputation: 152450

Use INDEX/MATCH/MATCH

=INDEX(datasheet!A:M,MATCH(A1,datasheet!A:A,0),MATCH(B1,datasheet!1:1,0))

Upvotes: 2

Related Questions