Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: ISnumber with Index/Match GoogleSheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default ISnumber with Index/Match GoogleSheets

    Can someone please help me, what is wrong here?

    if(ISNUMBER(A68),INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2),INDEX('Pricing & Availability'!A:E,match(A68,'Pricing & Availability'!A:A,0),2))

    I am getting the massage:

    "
    Error Did not find value '9178891612' in MATCH evaluation."

    A68 is indeed a number


    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ISnumber with Index/Match

    Bump

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: ISnumber with Index/Match

    If it is a number, why do you need A68*1?

    Is it a number or text in 'Pricing & Availability'!A:A?
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: ISnumber with Index/Match

    Looks like you have the formula the wrong way round, I suspect it should be
    if(ISNUMBER(A68),INDEX('Pricing & Availability'!A:E,match(A68,'Pricing & Availability'!A:A,0),2),INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2))

    Alternatively, you could just use
    INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: ISnumber with Index/Match

    Quote Originally Posted by Fluff View Post
    Alternatively, you could just use
    INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2)
    Will that work if A:A is text?
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  6. #6
    Board Regular
    Join Date
    Aug 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ISnumber with Index/Match

    .
    Last edited by shlomek12; Feb 12th, 2019 at 03:18 PM.

  7. #7
    Board Regular
    Join Date
    Aug 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ISnumber with Index/Match

    Quote Originally Posted by Fluff View Post
    Looks like you have the formula the wrong way round, I suspect it should be
    if(ISNUMBER(A68),INDEX('Pricing & Availability'!A:E,match(A68,'Pricing & Availability'!A:A,0),2),INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2))

    Alternatively, you could just use
    INDEX('Pricing & Availability'!A:E,match(A68*1,'Pricing & Availability'!A:A,0),2)

    I am getting the error:


    "Function MULTIPLY parameter 1 expects number values. But 'B01BV2CC56' is a text and cannot be coerced to a number."


    Sometimes A68 will be a Text cell and sometimes it will be a number cell, this is the reason why I need a "IF statement"

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,229
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: ISnumber with Index/Match

    Is this in Excel or some other spreadsheet software?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: ISnumber with Index/Match

    If you have a value of, say 1234567 is that showing as a value or text in 'Pricing & Availability'!A:A?
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  10. #10
    Board Regular
    Join Date
    Aug 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ISnumber with Index/Match

    Quote Originally Posted by Fluff View Post
    Is this in Excel or some other spreadsheet software?
    Google Sheet

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •