How can I use VLook UP or Index to fill in data from another sheet?

Here is the situation.

I am trying to fill in the blank cells in Sheet 1 which are the sold, profit cells using the data that is in sheet2.
I would like to keep the formatting the same.
What is the formula? Is this possible
I provided a mock-up workbook below.

Attachment 286035

-Matt

I copied and paste the code into the cell and I get this error #N/A
I have no clue what I am doing wrong.
What do you mean by "Also, make sure that both sets of headings are the same, otherwise this wont work "
Thanks
-Matt

Originally Posted by FDibbins

This is what you would normally use...
=INDEX(Sheet2!$A:$O,MATCH(Sheet1!$E4,Sheet2!$C:$C,0),MATCH(Sheet1!B$3,Sheet2!$A$3:$O$3,0))

But from the looks of it, you actually want to pull data from the row above? If so, then use this instead...
=INDEX(Sheet2!$A:$O,MATCH(Sheet1!$E4,Sheet2!$C:$C,0)-1,MATCH(Sheet1!B$3,Sheet2!$A$3:$O$3,0))

Also, make sure that both sets of headings are the same, otherwise this wont work

Wow. That was cool. I have been watching countless youtube videos and researching on google trying to figure this out. I am still trying to learn how you did that by reading the code. Can this method be possible by adding another 2 more identical sheets to find the item number and its data?

I upload a revised mock-up below.
-Matt

Would the formula begin with an IF statement or Index Match?
Or would I add an If statement to the Index Match formula already provided?
Thanks

Originally Posted by FDibbins

Is there a reason you have each month on its own sheet? It is often easier to have 1 data-base/sheet for all your data, and then run summaries and extracts from that

The only reason why I have each month on its own sheet is as follows -
- Each month is its own invoice for filing purposes
- Each Heading contains unique data such as date sold, fees for each item, ship cost, profit
- Also what if each invoice has over 500 items

The main sheet is the inventory
- Which tracks the purchase and sell date


Wouldn't it be complicated if I had all this information on 1 sheet?
I would have to do alot of scrolling.
Plus I try to keep all the information on 1 page (8.5 x 11)for printing purposes to file in a Binder.

What is your professional opinion?
Thanks

Originally Posted by FDibbins

Excel is designed to work with "database-type" structures, and having all the data on 1 sheet would simplify things actually, but we can work around that for what you need (it just gets a bit messy)

See the attached. I created a helper column in I, you can hide this if you want, it just pulls in the sheet number, and will need to be added to for additional sheets.
I then based the index/match on that helper

Wow. That is awesome. I been researching here all day trying to learn the formulas. I still dont quite understand how they work and the placement of certain functions. But, the easiest way for me to learn is to study the formulas that you provide and other users on here. I really appreciate the help.

On the other hand, what would the formula be if I didn't want the I column. And just the whole formula put together?
I really like the idea of the I column and I will keep it. I just want to keep studying the code and all the different methods of approach to fully understand excel.

Thanks, MATT