Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Lookup Data Meeting Criteria`

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

  • Lookup Data Meeting Criteria`

    I am trying to include a series of vlookups to find products by a product code from a database, an example is attached.

    I want to type a code into the box at the top and find the results containting but not uniquely being the input.

    eg typing 15 in automatically finds 15 using "=VLOOKUP(TRIM($C$6),'Main Database'!1:65536,2,FALSE)" but my database also contains 15eurd and 15rf etc and i want these to appear too.

    can i do this with vlookups with some clause that excludes a previously chosen entry?

    Any help appreciated
    Attached Files

  • #2
    Re: Vlookup Non Exact Match

    Can you attach a small sample of the whole database?

    Will the code being typed in always be the first two characters as in your example or does it need to allow for more flexibility?

    Comment


    • #3
      Re: Vlookup Non Exact Match

      thanks for the help - here's 2 files, an example of what i want and a bit of the database that relates to it - here i would want to type in 1140* and then the first entry 1140EFGDRF comes up at the moment and i would like the others in the 'family' to appear too
      cheers
      Attached Files

      Comment


      • #4
        Re: Vlookup Non Exact Match

        does anyone know if the application above is possible?

        Comment


        • #5
          Re: Vlookup Non Exact Match

          Hi,

          See the attachment.

          HTH
          Attached Files
          Kris

          ExcelFox

          Comment


          • #6
            Re: Lookup Data Meeting Criteria`

            Hi Krishan Kumar

            How to achive this???? kindly explain????

            regards

            Comment


            • #7
              Re: Lookup Data Meeting Criteria`

              Hi,

              Did you apply the formulas in your file? If you didn't get the desired results post back.

              I think all the formulas are very simple and self explanatory.
              Kris

              ExcelFox

              Comment


              • #8
                Re: Lookup Data Meeting Criteria`

                Hello Krishna

                I have went through the formuls in the sheet but I don't understand the process. kindly explain the process so that it will be very helpful.

                regards

                Comment


                • #9
                  Re: Lookup Data Meeting Criteria`

                  Hi,

                  Define a name for lookup value(Optional)

                  Here it is pCode

                  On Sheet1

                  D1 must be 0

                  D2 and copied down,

                  =IF(LEFT(A2,4)&"*"=pCode,LOOKUP(9.999999E+307,$D$1:D1)+1,"")

                  I used * as a wild card so that if the first 4 of product code matches pCode then it adds 1 to D1 and so on.

                  In E1,

                  =LOOKUP(9.99999999E+307,D:D)

                  Will give you the last value from Col D

                  We defined this as Count (Optional)

                  On Sheet2,

                  In A9 and copied down & across,

                  =IF(ROWS($A$9:$A9)<=Count,LOOKUP(ROWS($A$9:$A9),Sheet1!$D$2:$D$65536,Sheet1!A$2:A$65536),"")

                  ROWS($A$9:$A9)=1

                  if you drag down the number increases by 1

                  ROWS($A$9:$A10)=2 and so on.

                  now if the number <= Count then it performs LOOKUP function.

                  HTH
                  Last edited by Krishnakumar; June 14th, 2007, 00:45.
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Lookup Data Meeting Criteria`

                    Hi thanks man!! Krishna

                    Comment


                    • #11
                      Re: Lookup Data Meeting Criteria`

                      Krishnakumar,
                      what a brilliant solution - many thanks indeed

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X