+ Reply to Thread
Results 1 to 7 of 7

"vlookup" or "Index Match" Formula did not work

  1. #1
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    "vlookup" or "Index Match" Formula did not work

    Dear Experts,
    I am trying to vlookup value from sheet2 to sheet3 but the formula did not work. Please see the attachment file.

    Thank you in advance
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: "vlookup" or "Index Match" Formula did not work

    Probably a rounding error. Try this:

    =INDEX(Sheet2!B$3:B$65010,MATCH(ROUND($B3,2),Sheet2!C$3:C$65010,0))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    08-23-2018
    Location
    Ireland
    MS-Off Ver
    Office365
    Posts
    17

    Re: "vlookup" or "Index Match" Formula did not work

    The numbers in sheet 3 column B are not being properly seen as numbers.

    In column C of sheet 3 enter a formula and copy it down all your rows - start in cell C2

    Please Login or Register  to view this content.
    Copy down all rows,

    Then copy all those cells and then click on Cell B2 in sheet 3 and use a paste special / paste values to insert the numbers correctly.

    Tidy up column C by deleting the formulae

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: "vlookup" or "Index Match" Formula did not work

    Try this:

    Highlight B3:B177 > Data > Text to Columns > Finish

  5. #5
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    Re: "vlookup" or "Index Match" Formula did not work

    AliGW, Thank you so much.
    Is it possible to give directly results in column A? example avoiding the column D and directly reach the result.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: "vlookup" or "Index Match" Formula did not work

    Yes, but I am not sure what you want there. Maybe this?

    ="GAIL-MSA-XYZ-CS-" &INDEX(Sheet2!B$3:B$65010,MATCH(ROUND($B3,2),Sheet2!C$3:C$65010,0))&"-"&TEXT(COUNTIF($E$3:E3,E3),"000")

  7. #7
    Forum Contributor
    Join Date
    09-14-2012
    Location
    mumbai,india
    MS-Off Ver
    Excel 2003
    Posts
    367

    Re: "vlookup" or "Index Match" Formula did not work

    Just like the formula shown below, but without reference column "e"
    ="GAIL-MSA-XYZ-CS-" &IF(ISERROR(VLOOKUP(INDEX(Sheet2!B$3:B$65010,MATCH(ROUND($B3,2),Sheet2!C$3:C$65010,0)),Sheet1!$A$2:$C$300,2,FALSE)),INDEX(Sheet2!B$3:B$65010,MATCH(ROUND($B3,2),Sheet2!C$3:C$65010,0)),VLOOKUP(INDEX(Sheet2!B$3:B$65010,MATCH(ROUND($B3,2),Sheet2!C$3:C$65010,0)),Sheet1!$A$2:$C$300,2,FALSE))&"-"&TEXT(COUNTIF($E$3:E3,E3),"000")
    Last edited by sanju2323; 08-29-2018 at 08:32 AM.

+ 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. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  2. Replies: 4
    Last Post: 06-11-2017, 02:03 PM
  3. Replies: 3
    Last Post: 01-02-2014, 02:15 PM
  4. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  5. Strings Variables in Range("A1").Formula = "=index/match" ?
    By nadnerb5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2012, 05:07 PM
  6. [SOLVED] Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function
    By Fiebuls in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2012, 04:09 PM
  7. Replies: 3
    Last Post: 02-16-2011, 02:55 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1