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

Thread: Data and If statements, need some advice!

  1. #1

    Data and If statements, need some advice!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I've been working with Excel for four years or so now and have a pretty good knowledge of all the basic functions. I'm currently working with a big list of products, four columns of data and about 200 rows. A colleague has asked me to put together a spreadsheet capable of seaching the list of products for a code (that's been entered by the user), and then displaying the other information about the product (Two text fields and one numeric). I've previously accomplished this through use of nested if statements, although the previous sheet only had about 80 rows of information and took ages to put together.
    Is there a method of linking, searching and displaying information that I'm not aware of? My inital plan was to use a nested IF statement to check the entered data against the product codes to see if there's a match, but after seeing the amount of data I'm working with it just doesn't seem feasible. And then of course, I have to link the found code with it's information and display it all. I'd be very grateful for any input on this, it's driving me nuts!

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,544
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hello

    depending on your layout you could use VLOOKUP to retrieve the information or an INDEX/MATCH combination.

    Perhaps post a sample sheet?

  3. #3
    Thanks to our web blocker I've got no way of posting a file to the board. VLOOKUP hasn't got me anywhere, had a look over the INDEX and MATCH functions, as useful as they are they don't get me any closer to associating cells with each other. I relied heavily on the name manager in the last sheet, is there no other way of declaring values to Excel?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    Are you sure that Index/Match wouldn't work... have a look here for a tutorial: http://www.contextures.com/xlFunctions03.html

    You can upload the file to Dropbox or other free upload destination.


  5. #5
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,544
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Quote Originally Posted by Argartu View Post
    Thanks to our web blocker I've got no way of posting a file to the board. VLOOKUP hasn't got me anywhere, had a look over the INDEX and MATCH functions, as useful as they are they don't get me any closer to associating cells with each other. I relied heavily on the name manager in the last sheet, is there no other way of declaring values to Excel?
    You could use the INDEX function to return an array as described here but then again without a sample, we could be guessing for a couple of weeks :-)

  6. #6
    Manged to circumvent our blocker, the sample is hosted here: snk.to/f-c7t92knx

  7. #7
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,544
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Disregard this post

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    I am not sure why you say VLOOKUP or INDEX/MATCH won't work.

    If you enter the code in H2, then in I2:

    =IF($H2="","",INDEX(B:B,MATCH($H2,$A:$A,0)))

    or

    =IF($H2="","",VLOOKUP($H2,$A:$D,COLUMNS($H2:I2),0))

    copied across and down.


  9. #9
    It's mainly due to my ineptitude! I was having trouble applying the formulae from the tutorials, and was composing the formula in a completely different format. Brilliant solution, thank you so much!

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,499
    Articles
    0
    Excel Version
    Excel 2016
    It happens to all of us at times Don't feel bad.

    You are welcome


Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •