Recalling info into another row

[nak1777]

I am trying to recall info from the rows in another part of a worksheet when I type the first column. For example, I have over 300 rows of:

Apples 155 12oz red
Orange 120 10oz orange
Banana 50 11oz yellow
etc.

I want to select or type only the word:

Apples

And have the rest of the columns fill in with the rest of the info, like so:

Apples 155 12oz red

A filter would work well to select, except I need about 7 in the worksheet as it is a form and excel only allows one. I can’t seem to make the advanced filter work. An IF function would work, except I'd have to put all the words to match in " " marks, which would be very time consuming.

Anyone have any ideas of how I can recall the info from my list to the 7 lines I need to copy them into??

Any ideas would be greatly appreciated.

Thank you!

[martindwilson]

use a vlook up or index/match.
http://www.exceltip.com/show_tip/Loo...ions./775.html

[nak1777]

Those didn't work quite right - at least with me doing them - I've attached a txt of what I'm working on. It's a food service production report. What I need is, if I were to type "Fruit Juices" in A3....B3 would automatically fill in with the product code "A1-L" from B32, C3 would automatically fill in with "75 cs" from C32, G3 & H3 would fill in with "4.00 oz", respectivally, and so on. I need to have that option for each rows 3-27. It would be great if the list could be altered/updated and pull up the updated info when "Fruit Juices" or whatever is entered - like if the oz changed to 3.00 or something.

Would an advanced filter even work? I've been fooling with it all day to no avail.

Plz HELP!!!

[martindwilson]

i imported that to excel but not sure if its laid out correctly. zip and attach the excel file please.
vlook up would not work as data is not sorted . so index match is the way to go.

[BrooklynNY]

Try creating a validation list.

[nak1777]

zip file attached

Thank you!!

[Dean England]

It can be done with Vlookup. You could use something like this. I typed Fruit Juices in cell A13 and used the following formula in B13

=VLOOKUP($A13,$A$42:$I$340,2,0)

and pulled up the appropriate information. Copy the formula over to C13 and change the row number (shown bold in formula above) from 2 to 3 and you will have the correct info in that cell. However, your Lookup area is frought with peril. You are using spaces to indent some of your information and your lookups will not work correctly. It would be better to type your info into the cells and format them using indent, center or right to display it as you want it to show. If you have any questions, come back.

Hope that helps,

Dean

[nak1777]

Also, can I hide the #N/A's until they get filled in? Thank you so much!

[Dean England]

You need to change the column number in lookup range to the last column that contains information you need to pull.

=VLOOKUP($A13,$A$42:$I$340,2,0))

Change the bold area to $M$340 or $Z$340 or whatever. However, it's best not to make the range much larger than what you will actually use.

As an additional aid use the formula below if you don't wish to see #N/A when the Lookup cell is blank.

=IF($A13="","",VLOOKUP($A13,$A$42:$I$340,2,0))

See ya,

Dean

[nak1777]

Thank you a lot! That really saves the day!!

[Dean England]

Glad it helped. Thanks for the feedback.

See ya,

Dean