Announcement

Collapse
No announcement yet.

Help with Bottom up lookup

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help with Bottom up lookup



    Hello,

    We have 2 tabs. Tab 1 column A has a list of stores. For each store in Column A, I need a function to search and get the Region from Tab -2 in column B. In Tab -2 the Regions come 1st and then the stores. So the lookup should be bottom to top.

    Tab1
    Str0001
    Str0002
    Str0003
    Str0004
    Str0005
    Str0006
    Str0007
    Str0008
    Str0009
    Str0010
    Str0011
    Tab -2
    REG_0001
    Str0001
    Str0002
    REG_0002
    Str0003
    Str0004
    Str0005
    REG_0003
    Str0006
    Str0007
    Str0008
    Str0009
    REG_0004
    Str0010
    Str0011
    How can I get it? Please help.


    The expected result in column B
    COL A COL B
    Str0001 REG_0001
    Str0002 REG_0001
    Str0003 REG_0002
    Str0004 REG_0002
    Str0005 REG_0002
    Str0006 REG_0003
    Str0007 REG_0003
    Str0008 REG_0003
    Str0009 REG_0003
    Str0010 REG_0004
    Str0011 REG_0004
    Last edited by kumarkusa; July 14th, 2019, 13:44.

  • #2
    Attach an example file
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3

      Hi
      In Tab-1 B2

      =LOOKUP(2,1/(LEFT(OFFSET(Sheet2!$A$1,0,0,MATCH(A1,Sheet2!A$1:A$15,0)),3)="REG"),OFFSET(Sheet2!$A$1,0,0,MATCH(A1,Sheet2!A$1:A$15,0)))

      and fill down
      If the solution helped please donate to RSPCA

      Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

      Comment


      • #4
        example is attched
        Attached Files

        Comment


        • #5
          Hi kumarkusa,
          In sheet1 b2 =LOOKUP(2,1/(LEFT(OFFSET(Sheet2!$A$2,0,0,MATCH(A2,Sheet2!A$2:A$35,0)),3)="REG"),OFFSET(Sheet2!$A$2,0,1,MATCH(A2,Sheet2!A$2:A$35,0)))
          and fill down
          If the solution helped please donate to RSPCA

          Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

          Comment


          • #6
            Originally posted by pike View Post
            Hi kumarkusa,
            In sheet1 b2 =LOOKUP(2,1/(LEFT(OFFSET(Sheet2!$A$2,0,0,MATCH(A2,Sheet2!A$2:A$35,0)),3)="REG"),OFFSET(Sheet2!$A$2,0,1,MATCH(A2,Sheet2!A$2:A$35,0)))
            and fill down
            Thanks Pike...It worked ..

            Appreciate your help

            Comment


            • #7


              Hi kumarkusa,
              glad it work,, surprised the formula gurus did not come up with a better formula,,, must be a tricky one.
              Thanks for posting
              Last edited by pike; 4 weeks ago.
              If the solution helped please donate to RSPCA

              Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | The Smallman

              Comment

              Working...
              X