I am trying to create an inventory spreadsheet with two sheets in one workbook. The first sheet is where I want all info to appear. On this first sheet is a column (G) called VENDOR INVENTORY. I would like this column to be autofilled from the second sheet column (D) called INVENTORY ON HAND. This is the formula I thought would work: =VLOOKUP(InventoryOnHand!$A2,InventoryOnHand!$A$2: $D$49,4,FALSE). However I don't get the correct information for each line item.

I have attached images so you can all see what I am talking about. I am not sure if I should be using an "IF" in addition to my "LOOKUP" or if I should be using "VLOOKUP" which I also tried and got incorrect results.

Please help ASAP!

Attached Images

	MM image.PNG (13.1 KB, 16 views)
	IOH image.PNG (8.9 KB, 18 views)

I think you've misunderstood what VLOOKUP does—either that or your function contains a simple typo. What you want is You're starting with the value in VendorInventory!$A2, you see, and looking that up in InventoryOnHand; so the first argument in the VLOOKUP call must be the value on this sheet that you want to look up on the other sheet.

And by the way, I think it'll be simpler and work just as well to do it this way:That way you don't have to update the formula on the first sheet whenever you add new inventory items to the second.

Thank you BobBridges! This is EAXACTLY what I wanted. I used the second formula you provided as I like the idea of not having to adjust the formula when new inventory codes are added. Thank you so much.

Be aware that using VLOOKUP on entire columns will slow your sheet down.
You can use dynamic ranges or choose a large row number instead

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I think Peco knows more about this than I do, but for what it's worth when I specify just the columns, not the rows, I've never noticed my VLOOKUPs slowing down. I've noticed it when I'm doing a lot of VLOOKUPs, of course, thousands instead of dozens. My guess would be that Excel knows the last used row for each worksheet, and never bothers to look below it.

But then, as I said, if Pecoflyer says it'll slow it down I'm not inclined to scoff. Why do you say so, Peco? I've never done benchmarks, only watched for it. (And come to think of it, I always use whole columns; maybe the reason I never notice Excel slowing down is that I have nothing to compare it to.)

Well for a couple of thousand rows, it won't matter. When you reach larger numbers, the slowdown will be significant.
If one then continues retrieving data, say in the same row, using VLOOKUP, then adds some nice cosmetic formatting, or CF, trouble begins, sheets crash, etc..
So, IMO it's better to start things thinking about what will happen in the future with the sheet to avoid problems later on
But then again, that is a personal opinion . ( though I've seen it shared by many XL champions)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

This interests me. Let's say I have a workbook with two sheets named Main and Index. Main has 50 000 rows of data, and in column 5 of each row is a VLOOKUP pointing to Index. My usual VLOOKUP formula would go something like this: "=VLOOKUP(RC1,Index!C1:C3,3,0)". (I use R1C1 references by habit.) Now, let's take two examples:

1) Index has 15 rows. Would it make the lookups go noticeably faster to say "=VLOOKUP(RC1,Index!R2C1:R16C3,3,0)"?

2) Index has 20 000 rows. Would it make the lookups go noticeably faster to say "=VLOOKUP(RC1,Index!R2C1:R20000C3,3,0)"?

If Excel notices the last-used cell in Index, then I don't see why it would make a difference.