+ Reply to Thread
Results 1 to 8 of 8

Index Match from a cell populated from index match

  1. 12-14-2013, 06:15 PM #1
    MarcLewis
    MarcLewis is offline
    Registered User
    Join Date
    06-06-2012
    Location
    Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    25

    Index Match from a cell populated from index match

    Good Evening,

    I currently have a spread sheet which has an editable field in C3, B3 & E3 are populated by an index match formula to Tab 2, the formula is =IF(C3<>"",INDEX('2'!AG:AG,MATCH(C3,'2'!B:B,0)),"") for B3 and =IF(C3<>"",INDEX('2'!AO:AO,MATCH(C3,'2'!B:B,0)),"") for E3
    I now require A3 to perform an index match against the data in B3 to Tab 1 this is my current formula =IF(C3<>"",INDEX('1'!B:B,MATCH(B3,Schedule!B:B,0)),"") but it doesn't appear to working correctly.

    If anyone can figure this out it would be much appreciated.

    I have attached a sample of my spread sheet to help

    many thanks in advance

    index.xlsx
    Reply With QuoteRegister To Reply
  2. 12-14-2013, 07:18 PM #2
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Index Match from a cell populated from index match

    The numbers on sheet 2 are formatted as text.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  3. 12-14-2013, 07:34 PM #3
    MarcLewis
    MarcLewis is offline
    Registered User
    Join Date
    06-06-2012
    Location
    Yorkshire
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Index Match from a cell populated from index match

    Hi Jacc,

    I have formatted all the cells in all the tabs to both Number (with 0 decimals) & General & unfortunately this still hasn't solved the issue it still gives the wrong value
    Reply With QuoteRegister To Reply
  4. 12-14-2013, 07:53 PM #4
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Index Match from a cell populated from index match

    Not enough, they have to be converted too. Hit F2 then enter and it will sort of reregister as a number. If you have many lines use the Text to Column feature.
    Reply With QuoteRegister To Reply
  5. 12-14-2013, 07:54 PM #5
    FDibbins
    FDibbins is online now
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Index Match from a cell populated from index match

    Try this...=IF(C3<>"",INDEX('1'!B:B,MATCH(B3*1,'1'!A:A,0)),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  6. 12-14-2013, 07:55 PM #6
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Index Match from a cell populated from index match

    To match with sheet 1 formula should presumably be this

    =IF(C3<>"",INDEX('1'!B:B,MATCH(B3,'1'!A:A,0)),"")

    I get #N/A with that because, as Jacc says, you have a data type mismatch, text vs number.

    You can't convert text formatted values to numbers just by changing the formatting, try either converting B3 to a number like this

    =IF(C3<>"",INDEX('1'!B:B,MATCH(B3+0,'1'!A:A,0)),"")

    or you can use "text to columns" functionality to convert text values to numbers
    Audere est facere
    Reply With QuoteRegister To Reply
  7. 12-15-2013, 05:27 AM #7
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,368

    Re: Index Match from a cell populated from index match

    with VLOOKUP

    =VLOOKUP(VALUE(B3),'1'!A2:B3,2)

    Azumi
    Reply With QuoteRegister To Reply
  8. 12-15-2013, 05:30 AM #8
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,368

    Re: Index Match from a cell populated from index match

    Value function convert text to number value
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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, dynamic and connected without sorting helper column non-adjacent index match
    By Underling in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  2. 2010 - Need to match data and return specific cell value - SUMIF? MATCH? INDEX? HELP!
    By AJev in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2013, 02:10 PM
  3. [SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings
    By bkwins in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  4. [SOLVED] index match with row information offset from the match cell
    By smls in forum Excel General
    Replies: 7
    Last Post: 08-30-2012, 09:48 AM
  5. Index/Match/Match. Formula looking veritcal and horizontal headings to find a match
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-29-2012, 08:51 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1