+ Reply to Thread
Results 1 to 8 of 8

Index match to return Text instead of 0

  1. #1
    Registered User
    Join Date
    12-17-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    64

    Question Index match to return Text instead of 0

    Hello,

    I'm using the following formula to index and match, but I need it to return a "text" instead of 0

    =INDEX(All_Appended[#Data],MATCH($Q$3,All_Appended[client ID],0),MATCH(O11,All_Appended[#Headers],0))


    what should I add to it to return "No Data Found" instead of the 0?


    Thanks for helping.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Index match to return Text instead of 0

    =if(or(isna(MATCH($Q$3,All_Appended[client ID],0)),isna(MATCH(O11,All_Appended[#Headers],0))),"Match Not Found",INDEX(All_Appended[#Data],MATCH($Q$3,All_Appended[client ID],0),MATCH(O11,All_Appended[#Headers],0)))
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index match to return Text instead of 0

    Look at the attached, Maybe there's a shorter way

    =if(INDEX( All_Appended [#Data], MATCH($Q$3,All_Appended[client ID],0), MATCH(O11,All_Appended [#Headers] ,0))=0, "No Data Found",INDEX(All_Appended [ #Data],MATCH($Q$3,All_Appended [ client ID],0), MATCH(O11,All_Appended [ # Headers],0)))
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-17-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    64

    Re: Index match to return Text instead of 0

    --------------------------

  5. #5
    Registered User
    Join Date
    12-17-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    64

    Re: Index match to return Text instead of 0

    Thanks a lot for helping, but it's not working for me! Should it work with Dates fields as well? of just numbers?

    I want to post an example but I have a large table, maybe have to create a simple excel and upload.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index match to return Text instead of 0

    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  7. #7
    Registered User
    Join Date
    12-17-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    64

    Re: Index match to return Text instead of 0

    Thank you Belinda 200, this works perfectly

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Index match to return Text instead of 0

    you're welcome.

+ 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. trying to make INDEX(MATCH) with multiple text values to return.
    By Tvboy676 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-13-2019, 08:29 PM
  2. [SOLVED] Using Index Match to return a text value
    By Ozwilly in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-16-2018, 10:10 PM
  3. Index match with multiple criteria (text not return)
    By balajisrinath87 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-31-2017, 07:18 AM
  4. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  5. Use Index Match With Choose To Return Text Data from different sheets
    By WayOverMyHead in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-30-2016, 10:08 AM
  6. Replies: 3
    Last Post: 06-17-2013, 01:09 PM
  7. Trying to use match and index but return a text not numercial answer
    By parrot in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-04-2013, 10:44 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