Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Lookup Data Meeting Criteria`

  1. #1
    Join Date
    8th June 2007
    Posts
    4

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st February 2006
    Location
    London, UK
    Posts
    3,831

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    8th June 2007
    Posts
    4

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th June 2007
    Posts
    4

    Re: Vlookup Non Exact Match

    does anyone know if the application above is possible?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Vlookup Non Exact Match

    Hi,

    See the attachment.

    HTH
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  6. #6
    Join Date
    4th June 2007
    Posts
    49

    Re: Lookup Data Meeting Criteria`

    Hi Krishan Kumar

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

    regards

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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.

  8. #8
    Join Date
    4th June 2007
    Posts
    49

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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 at 00:45.

  10. #10
    Join Date
    4th June 2007
    Posts
    49

    Re: Lookup Data Meeting Criteria`

    Hi thanks man!! Krishna

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Display Data Meeting Criteria
    By welovevba in forum Excel General
    Replies: 3
    Last Post: April 23rd, 2009, 14:53
  2. Lookup Meeting Multiple Criteria
    By Jennifer.liljemark in forum Excel General
    Replies: 2
    Last Post: August 13th, 2008, 10:52
  3. SUM Of Data Meeting Many Criteria
    By pkk in forum Excel General
    Replies: 8
    Last Post: March 28th, 2008, 21:34
  4. List Data Meeting Criteria
    By tacnola in forum Excel General
    Replies: 5
    Last Post: July 17th, 2007, 14:17
  5. Sum Data Meeting 2 Criteria
    By Buliwyf in forum Excel General
    Replies: 4
    Last Post: October 11th, 2006, 04:56

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
  •  
porno