Excel 2007: how to relate a description with a material number

Question

Victor,

I am a co-op electrical engineering student and was having difficulties with a task I was given. The problem I'm having is that I have a huge list of items that need to go into a material sheet/list.

Ex.

 Item     Description        Qty

50515796 Pole, 75ft, wood 2 51795136 Pole, 40ft, wood 11

I was wondering if there was a way I could set up say the first sheet as the master material list (with all numbers/qty) and then on another sheet, or another section of the same sheet, simply type in the item number (51795136) and have the description come up in the box to the right of it?

If you require more information on the exact task at hand, feel free to email me and I am usually fast at replying (7am-4pm GMT, Mon-Fri).

Answer

Luke,

You didn't ask me this question - I found it in the question pool. Apparently it appears Victor has stopped answering questions or something and all his questions got dumped there. (Hopefully no ill event has befallen Victor).

anyway, If I have your table say with Item in M1 of sheet2

then in Sheet1 if I put 51795136 in cell B3, then in C3 I would put the formula

=if(B3="","",vlookup(B3,Sheet2!$M$2:$O$3,2,False))

to have the descripition appear in C3. In D3 I would put

=if(B3="","",vlookup(B3,Sheet2!$M$2:$O$3,3,False))

to have the quantity appear in D3.

the vlookup takes 4 arguments.

first: The value to be looked up Second: The range where the data is located (the column of values that are used for the lookup must be in the leftmost column of that range - in the example, in column M. Third: this is the column from which the value is returned. It is relative to the lookup range. So the 2 in the first formula means the second column of the lookup range, column N. So the value is returned from the row which is matched in column M and the value in column N is returned.

Fourth: This is true or False. If true it assumes the data is sorted on the first/lookup column. then vlookup will return the row for an exact match or the last row that is less than the lookup value. If false, the lookup data need not be sorted and it finds the row with an exact match. It should be noted that using false can be a much slower operation than if the data is sorted. (However, for general usage I have never found this to be a significant problem).

The vlookup function returns a #N/A error if the vlookup can not find a match.

Hope that answers your question. Post a followup question if you need more explanation or something is not clear.