Hi everyone,
I've attached a sample sheet with the data I have now. From that vertical list I need to make a horizontal one. I've tried to use match but it doesn't give me the result.
So basically I have a value and need to match to two other known values.
Not sure if I explained it correctly so the attached sheet should clear something!
sample_dieter.xlsx
If it is possible, please delete this post as I seem to have bad memory. I asked this question before and it was answered there.
In B21:
=INDEX($C$2:$C$16,MATCH(1,($A21=$A$2:$A$16)*(B$20=$B$2:$B$16),0))
confirmed by pressing CTRL+SHIFT+ENTER and then copying across and down.
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.
I'm getting a #VALUE error on that one.part gives the error.
Please Login or Register to view this content.
On my real file (which I can not share) I use the following:
There it gives#n/A on: MATCH(1;1;0)Please Login or Register to view this content.
Thank you for replying!
You need to enter it as an array using CTRL+SHIFT+ENTER. See it working in my attachment.
Forgot that! I had to change . too ; as well
That's because your locale is different to mine.Originally Posted by DieterKoblenz
Thank you! Last questions, when I copy it down it increases the column number which I do not want. How can you prevent this for a single column when copying?
It works perfectly in my sample file: you need to anchor using the $ sign where necessary in your own file.
Did this resolve the issue?
It took Excel several minutes to calculate all the values but it worked perfectlyMany thanks for your help (and the amazing quick responses).
You're welcome!
Formula:
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks