+ Reply to Thread
Results 1 to 5 of 5

Formula for INDEX and MATCH salary ranges within Job Catalog

  1. #1
    Registered User
    Join Date
    06-08-2022
    Location
    Almere
    MS-Off Ver
    Office 365
    Posts
    5

    Question Formula for INDEX and MATCH salary ranges within Job Catalog

    Hi Forum,

    First time poster and I am hoping to get some guidance and advice with the salary tool we are creating. It's all still very basic, but this formula would help us automate the role selection and salary matching.

    The spreadsheet currently holds two TABS, 1) is the Salary overview and 2) is the job catalog.

    The Jobcatalog has 5 coloms (A - E) which are:
    • A: Role (containing the job title)
    • B: Min (minimum salary associated with that job title)
    • C: 90% of max (90% of the salary associated with that job title)
    • D: 95% of max (95% of the salary associated with that job title)
    • E: Max (max salary associated with that job title)

    The Salary overview contains the following cells:

    Cel B3: dropdown list referencing job titles in Tab 2 Colom A
    Cel B8: dropdown list referencing salary range Titels in Tab 2 Colom B,C,D,E
    Cel B11: this would be the cell which will contain the MATCH and INDEX formula we need help with so it will show a value based on previous mentioned criterea.

    Purpose, to clarify, is that we can select a job title and salary range in B3 and B8 Tab 1 which will then auto populate B11 based on the Matches between B5, B8 TAB 1 and the INDEX in TAB 2.

    Hopefully this is a clear story, I tried to add the spreadsheet, but somehow it is not allowing me to do so.

    Attachment 783266

    Thank you!
    Attached Files Attached Files
    Last edited by Sorridere; 06-08-2022 at 10:38 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula for INDEX and MATCH salary ranges within Job Catalog

    You can add a sample workbook to one of your posts by following the guidelines in the yellow banner at the top of the screen. Do NOT try to use the paperclip icon to attach files, as it does not work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-08-2022
    Location
    Almere
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Formula for INDEX and MATCH salary ranges within Job Catalog

    Great!

    Thank you Pete, that worked. Should have read the manual

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Formula for INDEX and MATCH salary ranges within Job Catalog

    In B9

    =INDEX('Job Catalog'!$B$2:$E$21,MATCH('Salary package calculator'!$B$3,'Job Catalog'!$A$2:$A$21,0),MATCH('Salary package calculator'!$B$8,'Job Catalog'!$B$1:$E$1,0))

    in B10

    =B9*B7
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Registered User
    Join Date
    06-08-2022
    Location
    Almere
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Formula for INDEX and MATCH salary ranges within Job Catalog

    Worked perfectly John!

    Thank you very much for the formula and the solution. Much appreciated

+ 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. [SOLVED] Rearrange ranges for a catalog
    By esukei in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-09-2020, 12:25 AM
  2. Dynamic Index Match formula using named ranges
    By cross1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2019, 06:18 PM
  3. Index Match formula to VBA code as the ranges are too big
    By PaulM100 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2018, 11:56 AM
  4. [SOLVED] Using Index Match for Salary Lookup based on Name Key, Position, Years of service
    By julesmctavish in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-28-2017, 04:14 PM
  5. [SOLVED] index match array formula - replace cell references with ranges
    By nigelog in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-17-2014, 10:39 AM
  6. [SOLVED] Index Match Formula in VBA, that utilises Workbook dynamic named ranges
    By JamieW in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-13-2014, 09:39 AM
  7. Excel 2007 : Index and Match-prices and catalog numbers
    By Hlowmaster in forum Excel General
    Replies: 2
    Last Post: 10-30-2010, 04:25 AM

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