Data and If statements, need some advice!

Argartu

New member
Joined
Nov 12, 2013
Messages
4
Reaction score
0
Points
0
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!
 

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,779
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
Hello

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

Perhaps post a sample sheet?
 

Argartu

New member
Joined
Nov 12, 2013
Messages
4
Reaction score
0
Points
0
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?
 

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,779
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
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 :)
 

Argartu

New member
Joined
Nov 12, 2013
Messages
4
Reaction score
0
Points
0
Manged to circumvent our blocker, the sample is hosted here: snk.to/f-c7t92knx
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

Argartu

New member
Joined
Nov 12, 2013
Messages
4
Reaction score
0
Points
0
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!
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
It happens to all of us at times ;) Don't feel bad.

You are welcome :)
 
Top