+ Reply to Thread
Results 1 to 6 of 6

Null Value With Match

  1. #1
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Null Value With Match

    I have a workbook where I am matching on multiple values -- Using a Index match match function. Can you use match to match on two blank cells (or cells with no values?)?? Let's say I have this on worksheet 1 and want to match on horsehead & color to worksheet 2 in order to pull in the quantity. Well it works great when the color is specified, but any time I try to use this formula and the color is blank/null/omitted I gget a #N/A Returned? Can Index/match match on a null value?
    Some sample data:
    Item ------ Color ------- Quantity
    HorseHead ---- Red ----
    HorseHead ----- ----

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Null Value With Match

    Could you attached your workbook?
    Last edited by RobertMika; 02-10-2014 at 04:00 PM.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Re: Null Value With Match

    Book1.xlsx
    Quote Originally Posted by RobertMika View Post
    Could you attached your workbook?
    Well it would help if I attached the sample workbook. Sorry bout that.

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Null Value With Match

    Your value in Pivot is not Null is "blank"
    =INDEX(Sheet2!$G$4:$N$5,MATCH(Sheet1!A2,Sheet2!$F$4:$F$5,0),MATCH(IF(B2="","(blank)",B2),Sheet2!$G$3:$N$3,0))

    or if it was 0 then
    =INDEX(Sheet2!$G$4:$N$5,MATCH(Sheet1!A2,Sheet2!$F$4:$F$5,0),MATCH(IF(B2="","0",B2),Sheet2!$G$3:$N$3,0))

    To prevent Error return in case there is not match wrap your formula in IFERROR statement.
    Last edited by RobertMika; 02-10-2014 at 04:36 PM.

  5. #5
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Re: Null Value With Match

    Rev2.xlsx
    Quote Originally Posted by RobertMika View Post
    Your value in Pivot is not Null is "blank"
    =INDEX(Sheet2!$G$4:$N$5,MATCH(Sheet1!A2,Sheet2!$F$4:$F$5,0),MATCH(IF(B2="","(blank)",B2),Sheet2!$G$3:$N$3,0))

    or if it was 0 then
    =INDEX(Sheet2!$G$4:$N$5,MATCH(Sheet1!A2,Sheet2!$F$4:$F$5,0),MATCH(IF(B2="","0",B2),Sheet2!$G$3:$N$3,0))

    To prevent Error return in case there is not match wrap your formula in IFERROR statement.
    What about in this case, how would the same formula be applied but on different premise?

  6. #6
    Forum Contributor
    Join Date
    06-18-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    546

    Re: Null Value With Match

    Quote Originally Posted by jo15765 View Post
    Attachment 296369

    What about in this case, how would the same formula be applied but on different premise?

    Disregard my above -- all you have to do is switch the if statement you provided to the other MATCH statement. THANK YOU!

+ 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. How to work with Null and Not Null Vales
    By frankee_gee in forum Excel General
    Replies: 1
    Last Post: 10-15-2009, 03:46 AM
  2. INDEX and MATCH function, NULL value?
    By mabbutt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2009, 07:29 AM
  3. Code to replace null and not null strings
    By tigertim71 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2008, 05:07 PM
  4. Advanced filter - display null or non-null
    By Duddles in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2008, 11:17 PM
  5. [SOLVED] Null, "null", vbNull, vbNullString, vbEmpty
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2006, 08:30 AM

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