Data and If statements, need some advice!

A

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!
 
Hello

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

Perhaps post a sample sheet?
 
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?
 
Argartu said:
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?
Click to expand...

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 :)
 
Manged to circumvent our blocker, the sample is hosted here: snk.to/f-c7t92knx
 
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.
 
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!
 
It happens to all of us at times ;) Don't feel bad.

You are welcome :)
 
You must log in or register to reply here.
Back
Top