+ Reply to Thread
Results 1 to 3 of 3

If Index Match - then skip blank

  1. #1
    Registered User
    Join Date
    04-10-2017
    Location
    Sedgefield, South Africa
    MS-Off Ver
    2010
    Posts
    2

    If Index Match - then skip blank

    Hi there

    This topic has been covered before but as I am a novice I really don't understand the explanation. Please can someone assist me with my query:

    I have a main database with all my product info and pricing. I would like to create a price list based on specific criteria.

    Firstly, I have no idea which formula to use to pull the Item no (SKU) into the relevant product category tab. For this exercise, I just sorted my data and copied the SKU number and then used Index/Match to find the info, which works fine however, I don't want to copy and paste the SKU number each time, I would like the relevant tab to update with the new product etc. I am sure it is simple but as mentioned, I am a novice.

    Secondly, I would like the price list to only pull data that meets the criteria i.e. I only want items from Countries to appear in the section for that particular country. Or if I want to select based on a Category. I have tried IF / Index & Match and just cannot figure this out. It pulls up the correct first cell and then repeats it or pulls in data that doesn't match??

    =INDEX('Red Wine'!$A$3:$A$93;MATCH($B$6;'Red Wine'!J3:J93;0)) -
    =IF(Champagne!I3:I94='CHAMP Price List'!$B$6;INDEX(Champagne!$A$3:$A$94;MATCH($B$6;Champagne!I3:I94;0);0);0)

    I would appreciate any assistance.
    Thank you
    Lesley
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: If Index Match - then skip blank

    You have two easy methods: Filters, or power queries, neither of which need formulas.

    Filtering means only having the main sheet, and hiding columns you don't need, and filtering the ones that you do want, to show only the values that you need at the time that you need them. You can filter the sheet manually or with macros.

    Queries are simple if you have downloaded the Power Query add-in (if you actually have 2010). If you have 2016 or later, then just use power query. No advanced query methods needed for the basics - just filtering your large data set and removing unneeded columns and rearranging the ones that you want, and sorting to group by country. When you get a new data set, just refresh the query and the new data will be used.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    04-10-2017
    Location
    Sedgefield, South Africa
    MS-Off Ver
    2010
    Posts
    2

    Re: If Index Match - then skip blank

    Thank you Bernie. I will give both of those a try and let you know. I really appreciate it.

+ 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] Array Index Match to skip a particular value
    By SaadSiddiqi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-17-2019, 05:24 AM
  2. [SOLVED] Using INDEX MATCH formula to return values that will skip blank cells
    By mark_luke in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-12-2017, 06:11 PM
  3. Index match to skip duplicates
    By projectile in forum Excel General
    Replies: 5
    Last Post: 09-26-2016, 05:27 PM
  4. Index Match Skip Blank rows
    By choth21 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-10-2016, 01:19 AM
  5. Replies: 4
    Last Post: 03-24-2016, 03:20 PM
  6. [SOLVED] IF name match, copy, if blank skip to next row
    By marek256 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-29-2014, 04:11 AM
  7. index match, ..... skip result if blank value ...... jump to next match
    By gehawk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 04:42 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