+ Reply to Thread
Results 1 to 3 of 3

Create array that skips cells

  1. #1
    Registered User
    Join Date
    02-12-2020
    Location
    ohio
    MS-Off Ver
    10
    Posts
    5

    Create array that skips cells

    Hello,

    In column P I have a formula that finds the lowest price in columns G,K and M. This formula neglects values if they are 0.

    In column O i am trying to say who has the lowest price which is the headers of columns G K and M.(Tom, Frank and Tate)
    My current formula in O309 is: =IFS(MATCH(P309,G309:M309,0)=1, "Tom", MATCH(P309,G309:M309,0)=5, "Frank", MATCH(P309,G309:M309,0)=7, "Tate")
    the issue is that the array is finding a $0.03 in column I and reporting the match number as 3.
    Is there a way I can tell excel to not accept this value and keep going?

    Is there a better method to do this? I cannot rearrange the column orders either.

    Thank you,

    tate.PNG

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Create array that skips cells

    Post a sample workbook. See the yellow banner.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Create array that skips cells

    Maybe you try changing the MATCH(P309,G309:M309,0) argument into this:
    MATCH(P309,IF(G$3:M$3<>"Target",G309:M309),0)

    That will require your formula to be array entered. Once you're done typing the modification above, but still editing the formula, don't just hit Enter. Instead, press and hold Ctrl and Shift keys, then hit Enter.

    Good luck!

+ 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. Create a new array that skips row
    By kzh11411 in forum Excel General
    Replies: 3
    Last Post: 05-10-2017, 08:57 AM
  2. Replies: 14
    Last Post: 11-26-2015, 09:43 AM
  3. Script skips past numbers in an array.
    By Kalzin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-27-2015, 04:16 PM
  4. Replies: 2
    Last Post: 02-28-2014, 10:15 PM
  5. [SOLVED] Create a Formula That Skips Every n Cells
    By tj2434 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-20-2013, 05:30 PM
  6. Tab skips over cells
    By tomtheappraiser in forum Excel General
    Replies: 7
    Last Post: 04-01-2013, 01:01 PM
  7. [SOLVED] How can I create a list that skips zero values?
    By S.K.S. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2005, 10:06 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