+ Reply to Thread
Results 1 to 6 of 6

If & Index and Match

  1. #1
    Registered User
    Join Date
    10-16-2015
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    17

    If & Index and Match

    Could someone kindly advise the most appropriate formulae to use please

    In Tab 2016 List, Column B6 I have the word Exec, Cell B7 = G etc.

    In Column D6 I want to look up the average salary from another tab. Also in Column L6 I have the word Permanent or PTS

    My second tab is labellled Avg Rates

    Column = Exec, G, H, F etc.

    Column B has the Permanent Salary = $50,000 example

    Column C = PTS

    So my formulae would say If L6 = Permanent, Lookup in tab Avg Rates tab column A8 for Exec, and put the value in Column B8 which is permanent into cell L6

    Thanks

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: If & Index and Match

    Prob best posting a spreadsheet.
    Attach a sample spreadsheet with expected results, remove any sensitive data.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: If & Index and Match

    Agreed, post a sheet, but guessing that Column C in Avg Rates is either "PTS" or "Permanent" and has rates for both,

    =IF(L6="Permanent",SUMIFS('Avg Rates'!B:B,'Avg Rates'!A:A,"="&B6,'Avg Rates'!C:C,"Permanent"),"")

    or if you want values for both PTS and Permanent

    =SUMIFS('Avg Rates'!B:B,'Avg Rates'!A:A,"="&B6,'Avg Rates'!C:C,L6)
    Frob first, tweak later

  4. #4
    Registered User
    Join Date
    10-16-2015
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: If & Index and Match

    OK - Thanks for the input.

    I have attached a spread sheet that will better explain what I am trying to achieve.

    In Tab 2016 list, Column C I am trying to pull the Avg. rates from the second tab. It is looking at column B and L in the first tab and matching this to Columns A to E in second tab

    Please advise the best formulae to use.

    Thanks
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: If & Index and Match

    try below in C6 and drag down
    =VLOOKUP($B6,'Avg. Rates'!$A$8:$E$14,MATCH($L6,'Avg. Rates'!$A$7:$E$7,0),0)
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: If & Index and Match

    I think you have kept wrong information for
    Row no 10 on 1st sheet that is Andrew it should be 7500 as he is in permanent catagory

+ 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] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  2. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. [SOLVED] Index Match from a cell populated from index match
    By MarcLewis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-15-2013, 05:30 AM
  5. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  6. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  7. Replies: 5
    Last Post: 02-29-2012, 08:51 PM

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