+ Reply to Thread
Results 1 to 7 of 7

Index + Match doesn't work with text

  1. #1
    Registered User
    Join Date
    05-16-2008
    Posts
    35

    Index + Match doesn't work with text

    Problem.xls

    So the attached sheet, Cell H5 returns a value of N/A. IT should not!!! If I change the lookup value to a number (i.e. "Couple"), it works. But when left as text, it does not. Any way to make this work???

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Index + Match doesn't work with text

    1) Your MATCH() formulas are missing the 3rd parameter, 0 for exact match.
    =IF($F$5=4,INDEX(O7:O10,MATCH($G$5,$K$7:$K$10, 0)),
    IF(F5=3,INDEX(N7:N10,MATCH($G$5,$K$7:$K$10, 0)),
    IF($F$5=2,INDEX(M7:M10,MATCH($G$5,$K$7:$K$10, 0)),
    IF($F$5=1,INDEX(L7:L10,MATCH($G$5,$K$7:$K$10, 0))))))


    2) This formula seems to do what you want:
    H5: =INDEX($L$7:$O$10, MATCH(G5, $K$7:$K$10, 0), F5)

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,861

    Re: Index + Match doesn't work with text

    Hi blue,

    I did the same thing a few days ago. You left out the third argument in the Match function. You need to make it zero to "match exactly". Try this formula instead:

    =IF($F$5=4,INDEX(O7:O10,MATCH($G$5,$K$7:$K$10,0)),IF(F5=3,INDEX(N7:N10,MATCH($G$5,$K$7:$K$10,0)),IF($F$5=2,INDEX(M7:M10,MATCH($G$5,$K$7:$K$10,0)),IF($F$5=1,INDEX(L7:L10,MATCH($G$5,$K$7:$K$10,0))))))

    The reason it gives an error is, without the zero, your data needs to be sorted.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,486

    Re: Index + Match doesn't work with text

    I notice that your MATCH() functions are omitting the third argument (see description of arguments here: https://support.office.com/en-us/art...rs=en-US&ad=US ). As explained in the help file, by omitting this argument, Excel assumes a value of 1 for this argument which means that it assumes your lookup column is sorted in ascending order. This is obviously not the case in your spreadsheet. Choose one possible solution a) Add the third argument = 0 to the match functions or (b) sort the tables in K:O by column K in ascending order.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,785

    Re: Index + Match doesn't work with text

    You need to change your MATCH functions by adding ,0 at the end, to make it search for an exact match. The formula becomes:

    =IF($F$5=4,INDEX(O7:O10,MATCH($G$5,$K$7:$K$10,0)),IF(F5=3,INDEX(N7:N10,MATCH($G$5,$K$7:$K$10,0)),IF($F$5=2,INDEX(M7:M10,MATCH($G$5,$K$7:$K$10,0)),IF($F$5=1,INDEX(L7:L10,MATCH($G$5,$K$7:$K$10,0))))))

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    05-16-2008
    Posts
    35

    Re: Index + Match doesn't work with text

    Wow, that was quick. Thanks guys... that worked!

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Index + Match doesn't work with text

    Did you try the shorter version?

    =INDEX($L$7:$O$10, MATCH(G5, $K$7:$K$10, 0), F5)

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??
    By Underling in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2014, 09:01 PM
  2. Index Match formula doesn't seem to be working
    By ortmandj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2013, 08:04 PM
  3. Index Match Index Formula work slow
    By avk in forum Excel General
    Replies: 9
    Last Post: 03-07-2012, 02:19 PM
  4. Replies: 5
    Last Post: 01-06-2012, 08:00 PM
  5. Excel UDF using EVALUATE() method doesn't work with INDEX, OFFSET nor VLOOKUP
    By cryrus in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-02-2010, 04:53 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