+ Reply to Thread
Results 1 to 22 of 22

simple search function using nested IF and Vlookup statements

  1. #1
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    simple search function using nested IF and Vlookup statements

    How do I create the if (or and?) formula that ensures when two cells (A and B) in sheet 1 have values typed in, it looks ups and displays data from 4 columns(in sheet 2) in columns C D E F in sheet 1.
    There will always be data in sheet 2 entered in.

    Any ideas?

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: simple search function using nested IF and Vlookup statements

    Please attach a sample workbook with expected output for better understanding


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    I managed to solve that query using the match function but I have another one which is similar. Attached is a sample workbook (with formula in col C sheet 1) and expected output is below.
    On Sheet 1 - col a & b user to input data which is matched against col a & b in sheet 2. If this is correct col c, d,e,f will be populated with data from sheet 2, col c,d,e,f.
    col b on sheet 1 (and 2) is a unique Id number, however data in col a (in sheet2) may be different.
    Basically col a is a date and unique Id number (col b in both sheets) may have different dates (col a).
    How do I pull up the data in sheet 1 col c,d,e,f when col a has different date but col b has the same unique id number, once matched with col a and b in sheet 2.

    I am using excel 2002, however the attached workbook is excel 2007.

    Apologies if this does not make any sense. I've spent ages trying to figure this out!

    Thanks in advance.
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,624

    Re: simple search function using nested IF and Vlookup statements

    Try this in C2, sheet1:

    Please Login or Register  to view this content.
    Confrimed with Ctrl-Shift-Enter

    Drag down and across.

  5. #5
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    Thanks Bebo021999,
    Will that work in excel 2002 too?

  6. #6
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    I get a error message value.
    I should mention the data in sheet 2 ranges up to 10000 rows/cells but for testing purposes I am just using 3 rows.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,624

    Re: simple search function using nested IF and Vlookup statements

    Yes, I think it works in 2002.
    Adjust the range in the formula with your real data.

  8. #8
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    It still doesn't work I need to understand what the index function does...never used it before.

  9. #9
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,601

    Re: simple search function using nested IF and Vlookup statements

    Like this then?
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  10. #10
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    YES that has worked.
    Thank you so much JACC

  11. #11
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,601

    Re: simple search function using nested IF and Vlookup statements


    -----------

  12. #12
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    I forgot to ask about this formula, if the columns a & b in sheet2 are not next to each other and if col c,d,e,f are then the range will need to be adjusted? Or will the column number/s need to change?

  13. #13
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,601

    Re: simple search function using nested IF and Vlookup statements

    I see that I have not answered your last post here and now it is very old. I assume you have found out that you can move things around quite a bit without ruining the formulas.
    However the arrayformula solution that I posted earlier may not work well with 10 000 rows (unless you turn off automatic calculation).

    In this workbook is a slightly modified formula. It is more robust and it can handle more rows without slowing down because it is not an array formula. The concatenation is done in separate columns which saves a lot of time.
    By the way, don't forget to mark the thread solved if it answers all your questions. (it's under Thread Tools on the top of this thread).
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    Thanks JACC, I did find I can move this around without ruining the forumulas.
    Also thanks for the attached workbook. I might need to use this in the future.

  15. #15
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    Not sure if anyone can shed any light with this index match formula.
    So I have been using this code to find/match values typed in to cell A20 & B20 which works fine and displays data from the first 2 rows (from sheet1) It doesn't seem to read or match any other rows beyond the first 2.
    I don't know what I'm missing out.
    Please Login or Register  to view this content.
    Any ideas?

  16. #16
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,601

    Re: simple search function using nested IF and Vlookup statements

    Sorry for late reply.
    I'm guessing A20 should be $A20 and B20 should be B$20.

    Study the formulas carefully in the workbook I posted in post 13 and you will find that there are carefully placed $ -signs.
    The $ -signs will fix a reference vertically or horizontally or both ways. Hope this helps.

  17. #17
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    Thanks JACC
    I used $A$20 which works perfectly.

  18. #18
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,601

    Re: simple search function using nested IF and Vlookup statements

    Hmm... I realize now that your formula in post #15 is using a single column datatable (sheet1!$P$4:$P$2003). In that case you only need one MATCH formula, not two.
    I can't imagine that formula would work very well with $A$20.

    It would make much more sence if it looked something like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    What is it that you are trying to do anyway?

  19. #19
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    What I was trying to do is create a search like function where you have:-
    sheet 1 with multiple data including name, number and address (in separate columns)
    sheet 2 type in a name and number then address will appear on separate lines when the name and number is manually inputted in correct.
    The index match only worked for the first 2 i.e. for the first two names and numbers that appeared in sheet 1 but for some reason would not pull up the 3rd or more name, number when typed in sheet 2.
    All the data has unique names and number and addresses in.
    I'll try the single $ and see if that works. I am essentially matching 2 columns (when manually entered) which brings up data from 4 other columns(which are all in one row).

    I had to eventually resort to a simple vlookup where you type in a name and the corresponding address appears and this does the job.
    Last edited by cherryt; 08-20-2013 at 09:58 AM.

  20. #20
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    I think I found why the index match was not working. It is to do with the index array - the range of the table is causing me problems.
    This doesn't work because the range of the table
    Please Login or Register  to view this content.
    However when I use
    Please Login or Register  to view this content.
    This works and does what I want it to do.

    I still find it strange as to how the second formula works. I've been using the principles set out in this example with a few modifications - i.e. one match instead of two.
    http://www.myonlinetraininghub.com/e...atch-functions

  21. #21
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,601

    Re: simple search function using nested IF and Vlookup statements

    The reason why the first formula does not work is because the MATCH function only returns one number. In the INDEX function you have specified an array with 3 columns ($N$4:$P$27) but that would require a second number, perhaps a second MATCH function, to tell it which column to pick from. Since you don't have that Excel doesn't know what to do, it can't return an entire row to a single cell.

    In the second formula you have only a single column in the INDEX function. With a single column (or a single row) it is enough with one number to tell INDEX which cell value to return.


    Another part of these formulas is the & operator that is being used in the MATCH function. In order for & to work here you have to enter the formula with Ctrl + Shift + Enter (arrayformula).

    A good idea would be to make more simple versions of these formulas and create a mockup sheet with data where you can test them out until you fully understand how they work. Feel free to ask more as well.

  22. #22
    Registered User
    Join Date
    04-17-2013
    Location
    london
    MS-Off Ver
    Excel 2002
    Posts
    49

    Re: simple search function using nested IF and Vlookup statements

    I did try with 2 match function with N:P however only the first 2 rows of data were being searched/shown aswell as pushing each row/line to the above and I couldn't figure out why this was.
    It's all one big learning process to find out what works best for your needs as often I find there are more than 1 way to do something.
    Thanks for all your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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