• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX /MATCH WITH MULTIPLE RESULTS

T

terrydennis

Member
Index / Match with multiple results


I have been struggling with this for some time and would appreciate some help.


I have been trying to use the Index / Match combination but realise that , because there is more than one line of results for any person’s name , I need to be putting a formula in with CTRL+SHIFT+ENTER and include some means of stepping through the data table in the expression and including reference to ROW NUMBERS.


Let me try and explain what I have.


‘Members’ worksheet includes a cell with a drop-down list which enables me to select the person’s NAME (Cell D9) that I am interrogating.

Further down on this sheet is a table (B29:K31) containing the Index/Match expressions in order to output

‘Transfer Number ‘ , ‘Transfer Date’ , ‘Transferor’ , ‘Transferor address’ , ‘Transferee’ etc. There could be up to 3 output lines for any one person. I appreciate that the expression in each cell is basically the same except with a numeric reference in each expression to indicate the column number that is being referenced.


In the ‘Transfers’ worksheet , there is a database (A6:J114) with columns headed

‘Transfer Number’ , ‘Transfer Date’ , ‘Transferors Name’ , Transferors Address’, ‘Transferees Name’ etc , etc,


What should the basic Index/Match expression be to enable me to resolve my dilemma ?


I hope that I have explained the scenario clearly enough !
 
Take a look here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


You should be able to something similar, sort of like:

=IF(COUNTIF(RangewithNames,Name)<ROWS(A$1:A1),"",INDEX(ColumnWithInfo,SMALL(IF(RangewithNames=SpecificName,ROW(RangewithNames)),ROW(A1))))
 
Thanks for that very rapid response , Luke !


It all looks a bit daunting so you will have give me some time while I work through it !
 
..... but while I think about , perhaps you can answer this ! I am wanting to interrogate a persons NAME (the TRANSFERORS NAME).

You will note that above I refer to the 'TRANSFERS' worksheetwhich contains , among other things , the NAME but this is currently in Column C. Can I access data from cells either side of the Name that I have located on each row ? ............ or is that a dumb question ?
 
Yes, using the INDEX and MATCH, or INDEX and IF function that I used, you can look either left or right of the "lookup" column. Both formulas are just passing number to the INDEX function, which has it's own array, so it doesn't care where the MATCH function was originally looking at.
 
Luke


Apologies for taking time to reply to you !


First of , thank yopu so much for the help with my problem. It took me some time to undestand and apply your syntax to my particular application but it is now complete and working a treat !!


I am a competent Excel user , including macros,with experience over many years but when you come across unusual applications ,it makes you realise just how little I know compared to people like yourself !!


Many thanks again


Terry
 
No worries Terry, we're all here to try and learn a little more than what we know. =)
 
You must log in or register to reply here.
Back
Top