Lookups

• Please could someone explain the lookup for me, or even just tell me what to write! I've looked at the help files, the forum here and the add in on excel but I still get error messages.

In column A on the first tab (called parts) I have a list of part numbers.
In column A on the second tab (called inventory) I have a list of part numbers
In column S on the second tab I have the current inventory which I want to use the part number to find and bring into the first tab and put in column J

• I'm really sorry, but I just still don't seem to be able to do it. I'm not sure what I'm doing wrong.

Is there anyone that can take the information I've written in the first post and put together a lookup forumla that I can then just copy and paste?

Sorry to be such a pain, but I've spent the whole morning on this and I'm not getting anywhere.

• If you post your workbook someone will add the formula but you won't learn that way.

Are you using the function wizard?Click on the fx button next to the formula toolbar. This will open the wizard and you can follow the steps to create your VLookup

• Hi joop,

As Roy and jindon have indicated, you haven't really given us enough information to be able to help you.

I've guessed at your data structure from the small amount of info provided and come up with this:

=VLOOKUP(A1,inventory!\$A\$1:\$S\$10,19,FALSE)

This is the formula in J1 of the 'parts' sheet. It looks for the part number listed in A1 and tries to find it in the range A1:S10 on the 'inventory' sheet. It then returns the value from the 19th column ('S').

If this, the Help files, and all the other suggestions don't help you to resolve the problem then you really will need to provide a detailed explanation of your data structure and requirements, better yet - attach an example workbook.

• Thanks. I've attached my example. I've managed to get it working for some of the rows but i get an n/a for the others...

Thanks for the help.

ps.. I really am trying to learn this, that's why i've perservered with it for so long and haven't paid anyone else to do it!

Files

• well... if you are offering payment.....

• humm...i don't think that IS what i said....

• ok,

firstly, you have no data in column S on the week1 worksheet, so i have moved the data from column U and put it in column S

On the first worksheet in cell J3 use the following formula

=VLOOKUP(A3,week1!A:S,19,FALSE)

and then copy this down.

This does work.

James

• Hi joop,

Your initial formulas are looking in the range A1:C1 :?

They need to refer to your data range, in the example workbook this would be A1:U19.

Sorry James - stepped out of the office for a moment and you'd nipped in

• Thank you everyone.

I've had a play around with it and it's working now. The main difference (to me) seemed to be when I moved the stock level from the second tab, to the second column rather than in S or U or whatever.

does it make a difference if the column you're trying to return is miles away from the first column with no data in between?

I know there were other problems with the formula....

• no it doesn't make a difference whether or not you have data inbetween, but you have to remember that you have to select the range to go all the way over to the right hand edge of your data.

e.g. to get data in column S, you have to select the range in the 2nd argument to be A:S. You will get an #Ref! error, if you dont do this.

James

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!