Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-08-2016, 10:31 PM
paulzy95 paulzy95 is offline Excel - Index and Match Function ,First  Second and Third Match Windows 7 32bit Excel - Index and Match Function ,First  Second and Third Match Office 2016
Novice
Excel - Index and Match Function ,First  Second and Third Match
 
Join Date: Aug 2016
Posts: 9
paulzy95 is on a distinguished road
Post 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
Attached Files
File Type: xlsx Index Match Problem.xlsx (8.8 KB, 15 views)

Last edited by paulzy95; 08-09-2016 at 02:02 AM.
Reply With Quote
  #2  
Old 08-08-2016, 11:46 PM
xor xor is offline Excel - Index and Match Function ,First  Second and Third Match Windows 10 Excel - Index and Match Function ,First  Second and Third Match Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe like the attached.
Attached Files
File Type: xlsx MoreMatches.xlsx (10.4 KB, 28 views)
Reply With Quote
  #3  
Old 08-09-2016, 02:06 AM
paulzy95 paulzy95 is offline Excel - Index and Match Function ,First  Second and Third Match Windows 7 32bit Excel - Index and Match Function ,First  Second and Third Match Office 2016
Novice
Excel - Index and Match Function ,First  Second and Third Match
 
Join Date: Aug 2016
Posts: 9
paulzy95 is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 08-09-2016, 03:47 AM
xor xor is offline Excel - Index and Match Function ,First  Second and Third Match Windows 10 Excel - Index and Match Function ,First  Second and Third Match Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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.
Reply With Quote
  #5  
Old 08-09-2016, 06:47 PM
paulzy95 paulzy95 is offline Excel - Index and Match Function ,First  Second and Third Match Windows 7 32bit Excel - Index and Match Function ,First  Second and Third Match Office 2016
Novice
Excel - Index and Match Function ,First  Second and Third Match
 
Join Date: Aug 2016
Posts: 9
paulzy95 is on a distinguished road
Default 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.
Attached Files
File Type: xlsx xor question.xlsx (17.4 KB, 12 views)
Reply With Quote
  #6  
Old 08-10-2016, 03:31 AM
xor xor is offline Excel - Index and Match Function ,First  Second and Third Match Windows 10 Excel - Index and Match Function ,First  Second and Third Match Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

See comments in sheet xor.
Attached Files
File Type: xlsx xor question.xlsx (21.8 KB, 15 views)
Reply With Quote
  #7  
Old 08-11-2016, 04:45 AM
paulzy95 paulzy95 is offline Excel - Index and Match Function ,First  Second and Third Match Windows 7 32bit Excel - Index and Match Function ,First  Second and Third Match Office 2016
Novice
Excel - Index and Match Function ,First  Second and Third Match
 
Join Date: Aug 2016
Posts: 9
paulzy95 is on a distinguished road
Default

Thanks for that explanation, was really good, learn a great deal , cheers.
One issue I had with it is explained in attached spreadsheet, cheers.
Attached Files
File Type: xlsx xor question (2).xlsx (23.5 KB, 13 views)
Reply With Quote
  #8  
Old 08-11-2016, 05:06 AM
xor xor is offline Excel - Index and Match Function ,First  Second and Third Match Windows 10 Excel - Index and Match Function ,First  Second and Third Match Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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.
Reply With Quote
  #9  
Old 09-29-2016, 07:08 PM
paulzy95 paulzy95 is offline Excel - Index and Match Function ,First  Second and Third Match Windows 7 32bit Excel - Index and Match Function ,First  Second and Third Match Office 2016
Novice
Excel - Index and Match Function ,First  Second and Third Match
 
Join Date: Aug 2016
Posts: 9
paulzy95 is on a distinguished road
Default Xor

Hey man , just trying to get an equation that looks through the matrix to spit out the result.
Attached Files
File Type: xlsx Xor need help asap please, cheers.xlsx (9.3 KB, 9 views)
Reply With Quote
  #10  
Old 09-29-2016, 07:13 PM
macropod's Avatar
macropod macropod is offline Excel - Index and Match Function ,First  Second and Third Match Windows 7 64bit Excel - Index and Match Function ,First  Second and Third Match Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

See also:
http://windowssecrets.com/forums/sho...l=1#post734296
http://www.techsupportforum.com/foru...ml#post2567119
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 09-29-2016, 10:46 PM
xor xor is offline Excel - Index and Match Function ,First  Second and Third Match Windows 10 Excel - Index and Match Function ,First  Second and Third Match Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

You might want to take a look here.
Attached Files
File Type: xlsx paulzy95.xlsx (11.9 KB, 12 views)
Reply With Quote
Reply

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
Excel - Index and Match Function ,First  Second and Third Match 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:43 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft