Vlookup, Hlookup, otherlookup?

[jrcream]

I have a wksheet(named 'Invoice') that is used as a form to input the amount and type of certain items rented for a certain event as well as the date of set up and pick up of the items. After the form is completed a save form button is used to save the form in a different wkbook and clear the contents of the form. Each form has a corresponding number and I'm using this number in an if statement to record the setup and pickup dates in another wksheet('InventoryOut'). In this wksheet I have an array with rentals on the horizontal axis and Invoice # on the vertical axis. The if statement basically say "If the invoice # in 'InventoryOut'=the invoice # currently in the 'Invoice' wksheet then take the setup and pick up dates from 'Invoice' and record them in 'InventoryOut'.
My problem is trying to record the quantity under the correct type in 'InventoryOut'. I've tried using vlookup and hlookup, but I cannot identify my problem.
The attached file should bring more clarity to my words and includes my failed attempts at using the lookups.

Thanks

[Leith Ross]

Hello jrcream,

Having merged cells on a worksheet will confuse VLOOKUP. You will either need to reformat the invoice worksheet, so there are no merged cells, or use a UDF (User Defined Function) to return the information.

Your code seems to be checking the invoice for each type of rental, and not a total for each rental. Is that what you want?

Sincerely,
Leith Ross

[jrcream]

Having merged cells confuses Vlookup even if the merged cells are not in the range being looked up?

Your code seems to be checking the invoice for each type of rental, and not a total for each rental. Is that what you want?

No, I'm trying to get the total, but it has to fall under the correct type on the inventoryout wksheet.

Thanks Leith Ross

[jrcream]

Since the value to be looked up isn't in the far left column I'm attempting to use INDEX and MATCH to fix the problem, but ita proved more difficult then I imagined. Is there a more effecient way and if not whats wrong with my code?

=IF(A4=Invoice!C3,INDEX(Invoice!$A$15:$B$33,MATCH(D1,Invoice!B15:B33,0),MATCH(Invoice!A1,1:1,0)),0)

[jrcream]

I used a an IF statement with a combination of MATCH and INDEX

=IF('[autosaveinvoice.xls]Inventory Out'!A4=[autosaveinvoice.xls]Invoice!C3,INDEX([autosaveinvoice.xls]Invoice!$A$16:$A$33,MATCH('[autosaveinvoice.xls]Inventory Out'!D1,[autosaveinvoice.xls]Invoice!$B$16:$B$33,0)),0)

[jrcream]

Im just going to rephrase my question with pictures included for I feel my previous posts weren't that clear.

I have a button with an assigned macro that saves the invoice wksheet and I want to add some important features to. I have been trying to lookup the row in the InventoryOut wksheet (Exhibit E)*that contains the same value as the Invoice # in the Invoice wksheet(A). Once the row is looked up I'd like to trasfer a number of pieces of pertinent info into the InventoryOut wksheet from the Invoice sheet such as the pickup and setup dates(from B & C to G)*as well as the number of each item rented(from D to F).

[Leith Ross]

Hello jrcream,

Are you restating this as a reference to your solution or because you still need help???

Sincerely,
Leith Ross

[jrcream]

When I thought I had the solution I found out that my IF statement wouldn't work because everytime an invoice is saved the IF statement doesn't match (the invoice #s on the different sheets).
So I still need some help.

[jrcream]

In other words I need to add to the VBA code assigned to the button 'save invoice'.