+ Reply to Thread
Results 1 to 14 of 14

INDEX/MATCH using multiple worksheets where index is aplhanumeric

  1. #1
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    27

    Question INDEX/MATCH using multiple worksheets where index is aplhanumeric

    OK, I’ve finally bitten the bullet and am trying to use Index/Match in Excel 2016, mainly because the data set being reference is not in numerical order and the value I want to find is alphanumeric so vlookup won’t cut it. To compound the issue, the data set being reference is on a different worksheet in the workbook!

    I have 2 worksheets – one labelled Contractors & Employees (this contains employee details and tax status), the other is a timesheet labelled Wages & Salaries (this contains the calculations of employees hours, wages and tax to be deducted.

    I simply can’t get index/match to pull the correct value from Contractors & Employees worksheet into the Wages & Salaries worksheet.
    Data validation from a list works but needs to be re[peated for each column relevant to the employee’s circumstances. I ideally want to eliminate that so that I can select an employees ID number (alphanumeric) from a validation list on the Wages & Salaries worksheet and have the remaining columns auto-fill the relevant data from the Contractors & Employees worksheet.
    I’ve tried adapting many different formulae shown in tutorials on the web but none seem to address the issue of alphanumeric index on a different worksheet!
    Help?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    Named Range (called ID)
    ='Employees & Contractors'!$B$3:INDEX('Employees & Contractors'!$B:$B,MATCH("zzz",'Employees & Contractors'!$B:$B))

    DV in blue cells
    =ID

    B4, copied across and down:
    =IFERROR(INDEX('Employees & Contractors'!C$3:C$10,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$10,0)),"")
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    27

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    Hi Glenn
    Thanks for the attempt but it STILL woin't work in MY spreadsheet.
    My example that you used for your solution differs slightly as I cut it down to keep it simple but the relevant index/natch columns are same/similar EXCEPT the INDEX column in Employees & Contractors on MY sheet is Column B (B8:B27) and the MATCH is Wages & Salaries Column D ($d8 down, relative).
    I copied YOUR formula =IFERROR(INDEX('Employees & Contractors'!b$8:b$27,MATCH('Wages & Salaries'!$d8,'Employees & Contractors'!$B$8:$B$27,0)),"") to cell $E8 (note the changes).
    All I get is a frozen text version of the formula in $e8 and no relevant data flowing from the Employees & Contractors worksheet.
    What am I doing incorrectly?
    Do I need to do something with the cell range $b$8:$b$27 on the Employees & Contractors worksheet? (I ask because of what your solution shnows on first line "Named Range" - if so, where does this go and how do I enter it?)
    Sorry to be a pain.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    Given your Q, I assumed that you were aware of Named Ranges. CTRL-F3 to view/edit the NR on MY sheet.

    Check the formatting on the cells with formulae? Is it set to text? If so, reformat as General. Then it will/should/might work. Formatting as TEXT disables formulae.

  5. #5
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    27

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    Not at all familiar with Named Ranges nor how to use them.
    Where do I enter this formula?
    To where is is replicated - if that is the case?
    Last edited by USAOz; 11-05-2020 at 03:09 AM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    On my sheet, CTRL-F3 to view/edit the Named Range called ID. Basically it dynamically selects the exact range of the names in that column, so that you don't have to adjust the range that is used for the dropdown. Excel does it for you if you use =ID as the criterion for the data vsalidation.

    What's the situation regarding cell formatting as mentioned in my last post?.

  7. #7
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    27

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    Reformatting does not change the display problem. No matter how I enter the formula (copy/paste or type in manually), the cell just displayes the text entered, not the result! Weird.
    It works fine in the example you supplied but if I plug it into the actual worksheet it fails!

    Into what cell do I type the Named Range formula?

    I can't see where ID is mentioned in the INDEX/MNATCH formula you provided.

    Sorry to be a pain and I really do apprfeciate the time you arfe taking to resolve my problem.

    (BTW, are yoiu still inlocked down Eire? I have 2 very good frinds that live in Cork and they are suffering social withdrawal! Be safe mate!)

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,283

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    According to post #3 Glenn's IFERROR(INDEX... formula is supposed to go into cell E8.
    Select cell E8 and then select Clear All (Home tab > Editing pane) and then paste the formula into cell E8.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    Back again. Driving / flying for 48 hours. How is it working right now?

  10. #10
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    27

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    Off and on - very erratic.
    I get the function working in one row/column and then fill down/across and somethines it works, other times it doesn't.
    I have several issues now relating to a basic index/match, where a column fills correctly EXCDPT for one name from the index list and nothing I do will fiox it.
    Often, if I copy/paste, the error that I've entered too many parameters pops up yet the formula worked fine without that error in the original cell.
    More perp[lexing is the inability to sum a range of values that match both an index marking a quarter and a user name. Is there some way to index/match multiple indices in the same cell?

    I am attaching a stripped down version of a workbook with three sheeets that are are interrelated yet either fail to calculate correctly or across the worksheets.

    I don't expect you to spend your valuable time on this for too long without compensation but I would appreciate you having a look at what I've done (or not done) - your efforts so far have been awesome and well received. Thank you.

    BTW, what is FLYING? Is this some new method of transport? (Unless you are a private aviator, microlight pilot or hangglider, in which case, welcome to the elite group!)
    Attached Files Attached Files

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    I think you need to point out what is not working... and where...

  12. #12
    Registered User
    Join Date
    09-10-2015
    Location
    Caringbah, NSW, Australia
    MS-Off Ver
    Office 365
    Posts
    27
    Quote Originally Posted by Glenn Kennedy View Post
    I think you need to point out what is not working... and where...
    Shall I list the cells for you?

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    Yep. That would be a lot quicker!!

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: INDEX/MATCH using multiple worksheets where index is aplhanumeric

    One or two per formula tyep would be perfect.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-22-2020, 04:47 PM
  2. [SOLVED] Index/Match across multiple worksheets
    By Rookie2016 in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 02-05-2018, 01:48 PM
  3. [SOLVED] Best way to Index/Match across multiple worksheets?
    By alexx579 in forum Excel General
    Replies: 20
    Last Post: 07-24-2017, 10:04 AM
  4. Index & match SUM multiple worksheets
    By Thito in forum Excel General
    Replies: 12
    Last Post: 04-02-2014, 05:15 PM
  5. Index match multiple worksheets
    By rrivera616 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-28-2013, 07:03 PM
  6. index and match across multiple worksheets
    By pertenax in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-21-2012, 03:15 AM
  7. Excel 2007 : Index and Match using multiple worksheets
    By lola12345 in forum Excel General
    Replies: 1
    Last Post: 10-27-2011, 03:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1