+ Reply to Thread
Results 1 to 4 of 4

Index Match Formula Issue - Lookup Value is a Formula

  1. #1
    Registered User
    Join Date
    06-27-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Microsoft 2013
    Posts
    36

    Index Match Formula Issue - Lookup Value is a Formula

    Hi,
    I am having trouble with my Index Match formula. The lookup value (column C) is in the adjacent cell but it is a formula. It works on the cells that have letters in them. But, the ones with all numbers if does not. If I add a value function to the formula it works. But, only on cells with all numbers. Would like to find a way it would work with both all numbers and cells with letter number combinations. Please see my attached example.
    Thanks in advance for the help.
    Attached Files Attached Files
    Last edited by dodgemain; 06-06-2016 at 12:06 PM.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Index Match Formula Issue - Lookup Value is a Formula

    Enter this formula in D2 and copy across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 06-06-2016 at 11:34 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Microsoft 2013
    Posts
    36

    Re: Index Match Formula Issue - Lookup Value is a Formula

    Hi,
    Thanks for the fast response. The formula work great! But, what does the 10^308 do?
    Thanks

    Quote Originally Posted by AlKey View Post
    Enter this formula in D2 and copy across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Index Match Formula Issue - Lookup Value is a Formula

    This is an incredibly large number that can be entered in Excel and most likely doesn't exist withing the lookup_vector. Since the LOOKUP function typically works on a sorted (ascending order) range it will go for the last row assuming that this is where the largest value exists. However, when used with SEARCH or FIND functions, the only number it will find is the one returned by the search function.

    Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

+ 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] Index Match Formula doesn't recognize output from Right Formula as lookup value.
    By GoGators in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-10-2015, 11:33 AM
  2. [SOLVED] INDEX + MATCH(MAX) formula issue
    By Deschain3 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-04-2014, 07:02 PM
  3. #N/A issue with index/match formula
    By jorjacman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-24-2014, 10:00 AM
  4. [SOLVED] Index/match formula issue
    By Sychoanalyze in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2012, 04:25 PM
  5. [SOLVED] Index/Match Formula Issue
    By Tamarissa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-23-2012, 06:35 PM
  6. Index/Match Formula Issue
    By nross in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2012, 08:17 PM
  7. [SOLVED] Index Match formula issue
    By Ozwilly in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-06-2012, 03:09 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