LinkBack URL
About LinkBacks
Dear all,
how can I get the name of the element in the row?
thank you in advance.Capture.JPG
Dear all,
how can I get the name of the element in the row?
thank you in advance.Capture.JPG
In I2 then copied down.
=IFERROR(INDEX($A$2:$A$7,MATCH($I$1,INDEX($B$2:$F$7,,MATCH($H2,$B$1:$F$1,0)),0)),"")
Last edited by kvsrinivasamurthy; 04-25-2019 at 03:51 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Welcome to the forum!
In I2 copied down:
=INDEX($A$2:$A$7,MATCH(1,OFFSET($A$2:$A$7,,MATCH(H2,$B$1:$F$1,0)),0))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi @AliGW your formula doesn't work because I see there are 2 criteria, namely columns header and values
It works perfectly - did you test it? I did (see the final two columns below):
Excel 2016 (Windows) 32 bit
AliGW 1 AliGW 2 P P1 P1 P2 N P6 P6 P3 L P1 P1 P6 M P5 P5 P6 MI P3 P3 P6
Sheet: Sheet1
J2: =INDEX($A$2:$A$7,MATCH(1,OFFSET($A$2:$A$7,,MATCH(H2,$B$1:$F$1,0)),0))
K2: =INDEX($A$2:$A$7,MATCH(2,OFFSET($A$2:$A$7,,MATCH(H2,$B$1:$F$1,0)),0))
Last edited by AliGW; 04-25-2019 at 04:29 AM.
@Excelsar - if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Thank you very much. It works fine.
Thank you very much. ıt works fine.Originally Posted by kvsrinivasamurthy
Thank you very much. It works fine.
No problem.
Dear kvsrinivasamurthy ,
I was trying to understand how you build the formula and that's why I was trying to split it to it's components. I couldn't solve/understand some parts.
Could you please split this formula to 4 parts in different columns for each component?
I tried but I could not make it. For example; this part " INDEX($B$2:$F$7,," gives N/A.
I was trying this:
I2: MATCH($H2,$B$1:$F$1,0)
J2: INDEX($B$2:$F$7) reads from I2
K2: MATCH($I$1.. reads from j2
L2: INDEX($A$2:$A$7 reads from k2
The formula works fine, thank you again.
I suggest you remove the SOLVED tag, otherwise this request might go unanswered.
I did it, thank you...
What exactly are you trying to do now?
I am trying to learn how to build that formula.
For this I needed to split it to small pieces.
You can't split it into bits and expect those bits to work on their own. Do you want a breakdown of what each section does?
In the case of mine:
=INDEX($A$2:$A$7,MATCH(1,OFFSET($A$2:$A$7,,MATCH(H2,$B$1:$F$1,0)),0))
INDEX($A$2:$A$7, - this tells Excel where to find the result data - the range from which the results will come.
MATCH(1,OFFSET($A$2:$A$7,,MATCH(H2,$B$1:$F$1,0)),0) - this bit tells Excel which row of the results data range to return.
MATCH(1, - this bit tells Excel that we are looking for the number 1 in the lookup range.
OFFSET($A$2:$A$7,, - this bit tells Excel that the range we are looking for will be offset from the result data range by ...
MATCH(H2,$B$1:$F$1,0)) - ... the number of columns based on the position of the value of H2 in the range $B$1:$F$1.
0) - finally, this bit tells Excel that we are looking for an exact match.
Dear AliGW
Thank you very much.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks