+ Reply to Thread
Results 1 to 6 of 6

Index formula returns values that need to be ignored. Need syntax that excludes values.

  1. #1
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Index formula returns values that need to be ignored. Need syntax that excludes values.

    I have an index formula:

    =IFERROR(INDEX([43.xlsx]Who_Did_What!$D2:$D10000,MATCH(1,INDEX(([43.xlsx]Who_Did_What!$C$2:$C$19000=G3)*([43.xlsx]Who_Did_What!$E$2:$E$19000<>"")*([43.xlsx]Who_Did_What!$D$2:$D$19000>0),0),0)),"")

    See attached a replica "example 2".
    The above formula was in a live workbook which i am trying to fix this problem - i cannot attach this for data protection reasons. However the replica is exactly the same, just replaced the data with fakes.

    The formula was in J3,
    It is pulling the data from "43.xlsx" which i have attached another replica of.

    Currently i understand this formula ignores "-1"s and "0"s, which are in column D, employee number. Issue is now is that there are some employee ID numbers i wish to exclude or for the formula to not return.

    These are 896, 872, 873, 874, 875, 876, 877, 878, 879, 880.

    I don't know how to include the above ID numbers in the formula.

    I am expecting to see GEFF in J3 (formula is looking up "LARGE CAR", G3"). Not MACK as MACK has ID 874.

    Hope this makes sense
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Index formula returns values that need to be ignored. Need syntax that excludes values

    may the below will work

    Please Login or Register  to view this content.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Index formula returns values that need to be ignored. Need syntax that excludes values

    This was working until i tried to add another number to the exclude list, 905,

    I thought i would just need to add it to {896,872,873,874,875,876,877,878,879,880} so its {896,872,873,874,875,876,877,878,879,880,905}

    But when i do the cell returns blank...

    What did i do wrong?
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Index formula returns values that need to be ignored. Need syntax that excludes values

    Did you also add to the other array (highlighted in red, may need to scroll formula right)?

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I think that array need the same number of values as in the array of values to exclude. I could be wrong however
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  5. #5
    Registered User
    Join Date
    07-18-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    84

    Re: Index formula returns values that need to be ignored. Need syntax that excludes values

    Oh no i didn't

    Do i need to add an extra 1? I tried this but got an error message.

    I attached the workbook i used as an example to the last reply

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Index formula returns values that need to be ignored. Need syntax that excludes values

    I tested modifying the formula as Zer0Cool suggests and got what I expected in cell J3 (GEFF)
    After adding ,905 to the first array and ;1 to the second it should read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    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.

+ 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: 4
    Last Post: 07-25-2017, 12:26 PM
  2. Formula that returns only values of cells and doesnt return blank/NA values
    By pageandrewr1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2016, 12:32 PM
  3. Index Match that returns all non zero values in row
    By kvarner in forum Excel Formulas & Functions
    Replies: 44
    Last Post: 03-03-2016, 06:41 PM
  4. Ranking Formula that Excludes Values
    By intercon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2014, 06:11 PM
  5. Index formula that returns only unique values based on cell value
    By wakerider05 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-05-2014, 09:37 AM
  6. Replies: 3
    Last Post: 11-06-2012, 03:19 PM
  7. Replies: 1
    Last Post: 10-23-2012, 12:08 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