+ Reply to Thread
Results 1 to 11 of 11

Row number based on lookup value

  1. #1
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Row number based on lookup value

    Hello Boss.

    There are lot sites in my company across the world.What are the sits are ready for dispatch's,every time I use copy and paste method.I need row number based on lookup value.

    In Row A2:A21 Country Name,i have enter row number 2 return the name united states.

    Please help me.file attached.

    Country

    United States
    China
    Japan
    Germany
    France
    Brazil
    United Kingdom
    Italy
    India
    Russia
    Canada
    Australia
    Spain
    Mexico
    South Korea
    Indonesia
    Netherlands
    Turkey
    Switzerland
    Saudi Arabia

    Desired Results.

    Desired Results

    2 United States
    10 India
    11 Russia
    16 South Korea
    18 Netherlands
    8 United Kingdom
    9 Italy
    5 Germany
    4 Japan
    3 China
    15 Mexico
    16 South Korea
    17 Indonesia
    18 Netherlands
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Row number based on lookup value

    I'm still not clear on what you want specifically because you could just put the number 1 in cell b1 then 2 in B2 and drag down and you'd have the numbers representing the numbers of the rows. Or this formula in cell B2 dragged down also does the same thing... =ROWS($1:2)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,215

    Re: Row number based on lookup value

    To get the country from row number

    =INDEX($A$1:$A$21,D2)

    to get row number from country

    =MATCH($E2,$A$1:$A$21,0)

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Row number based on lookup value

    Maybe after seeing John's reply I see better what you might want, this is your file with my version of it. Similar to John's but incorporates my suggestion from post #2.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,215

    Re: Row number based on lookup value

    @Sambo kid,
    Like you I wasn't really sure what was required: this is the problem with only getting a snippet of how an application is constructed!

  6. #6
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: Row number based on lookup value

    THANKS YOU SO MUCH FOR YOUR HELP MR.JohnTopley AND Sambo kid

    COLUMN D AND E MY DESIRED RESULTS.

    MY ACTUAL REQUIREMENT I HAVE ENTER ROW 2 IN COLUMN D2 RETURN THE NAME US-"United States"

    D3=ENTER ROW NUMBER RETURN THE NAME US-"Russia.

    WITHOUT ADDITIONAL COLUMN IF POSSIBLE.
    Attached Images Attached Images
    Attached Files Attached Files

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: Row number based on lookup value

    Try =INDEX($A$1:$A$100,D2)

    PS Be aware that writing in capital letters is the equivalent of shouting. As we are all friends here, no need to.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,215

    Re: Row number based on lookup value

    You were given the answer in post #3
    To get the country from row number

    =INDEX($A$1:$A$21,D2)
    ...and in #4 and now from Pepe in #7!

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: Row number based on lookup value

    Sorry John
    forgot to read the preceding posts

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,215

    Re: Row number based on lookup value

    @Pepe ... I was complaining to the OP (but perhaps it was not clear) !!! so no apology is needed from you.

  11. #11
    Forum Contributor
    Join Date
    08-07-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    372

    Re: Row number based on lookup value

    Mr.JohnTopley:Mistake is mine am extremely sorry.To little bit confuse.Your formulas working fine way. Thanks you so much consider my query again.

    Pepe Le Mokko :Thanks for your help and suggestion in future do not write in capital letters.

+ 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. Unique Number based on conditions with lookup
    By pauldaddyadams in forum Excel General
    Replies: 6
    Last Post: 01-07-2015, 11:19 PM
  2. [SOLVED] Lookup value based on specified number sequences
    By 00pumpkin in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-08-2014, 12:10 PM
  3. Replies: 13
    Last Post: 12-19-2012, 02:38 PM
  4. Lookup Column number in a table based on a cell value
    By electricmice in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-22-2012, 01:08 AM
  5. lookup based on a row number
    By chrisrowe_cr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-15-2005, 10:05 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