#1
|
|||
|
|||
Excel - Index and Match Function ,First Second and Third Match
Hey Guys,
I've been stuck on this problem, It has real world application. As a simplification: I have a fixed table with: a | Gold b | Silver b | Aluminium c | Tin c | Copper c | Hydrogen d | Helium e | Lithium Now I have a different table, where I need an excel function to input and fill out this table based on the criteria of the table above. [Column] [Column] [Column] [First Match] [Second Match] [Third Match] a b c d e I need a function that would populate the table like so: [Column] [Column] [Column] [First Match] [Second Match] [Third Match] a Gold N/A N/A b Silver Aluminium N/A c Tin Copper Hydrogen d Helium N/A N/A e Lithium N/A N/A So far I can get the first column to work, but I can't get a function that will produce a second and third match. So far the most progress I have made has been for the first column which works with a =index match function. I have attached an excel document with my progress of this. Any help will be most appreciated! Thanks for reading! Cheers, Best Regards Last edited by paulzy95; 08-09-2016 at 02:02 AM. |
#2
|
|||
|
|||
Maybe like the attached.
|
#3
|
|||
|
|||
xor, you my friend are a legend! , like Thankyou So much!!! , For my own understanding I'm going to work backwards from what you have done to learn it, is there a website that you learnt this from that I could utilise?
|
#4
|
|||
|
|||
Most of what I know I have learned by solving practical problems and by trying to help other Excel users in groups like this one. One important thing in understanding complex formulas is to master Excel's Evaluate Formula tool/F9 technique.
There are almost an infinite number of websites with Excel stuff. If you are specifically interested in array formulas you might want to take a look at: https://www.ablebits.com/office-addi...nctions-excel/ http://www.emailoffice.com/excel/arrays-bobumlas.html The last one is superb but really advanced. |
#5
|
|||
|
|||
Xor , just trying to understand
Firstly thanks again for your help yesterday, for my own learning I'm trying to get my head around those arrays u used. see attachment of excel doc.
|
#6
|
|||
|
|||
See comments in sheet xor.
|
#7
|
|||
|
|||
Thanks for that explanation, was really good, learn a great deal , cheers.
One issue I had with it is explained in attached spreadsheet, cheers. |
#8
|
|||
|
|||
CELL("row";arrCodes)+1 returns 4. That is the row number of the upper left cell in range arrCodes =3 (+1).
But the formula says: -CELL("row";arrCodes)+1 which returns -3+1 = -2 (as it should). Please note that when you highlight CELL("row";arrCodes)+1 and press F9 it is correct that you see -4 but that's not the result of what you have actually highlighted but the result of the (not highlighted minus sign) and the highlighted part which is 4. When you include the minus sign in what you highlight Excel will evaluate -CELL("row"arrCodes) to -3 and finally the +1 which gives the result -2. Last edited by xor; 08-11-2016 at 10:41 PM. |
#9
|
|||
|
|||
Xor
Hey man , just trying to get an equation that looks through the matrix to spit out the result.
|
#10
|
||||
|
||||
See also:
http://windowssecrets.com/forums/sho...l=1#post734296 http://www.techsupportforum.com/foru...ml#post2567119
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
You might want to take a look here.
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Index Match Function across different worksheets | shay_mt | Excel | 2 | 04-27-2015 06:04 AM |
Index match vba | grexcelman | Excel | 0 | 03-05-2015 10:55 PM |
UDF multiple IFs INDEX-MATCH | grexcelman | Excel Programming | 0 | 02-22-2015 04:20 PM |
Index Match function | jackzha | Excel | 5 | 12-03-2014 12:43 PM |
Moving a Index/Match function | FraserKitchell | Excel | 2 | 01-19-2010 09:38 AM |