Requesting help with multiple tabs

[Roofpigeon]

Hi,

So I have a request at my job to set up a sheet where data is being loaded in from another tab.


The tabs are as followed:

- Order
- Supply
- Avg2015
- Avg2016


So the data has to be loaded into the Order tab which contains the following headings:

Order:
SKU (product number)
Name
Supply
Avg2015
Avg2016

Supply:
SKU (product number)
qty (quantity)

Avg2015 & Avg2016:
SKU (product Number)
Average

Naturally these tabs have more data but this is the data that's required to be placed on the Order tab

So SKU and Name are present on the Order tab, but i need to load in supply from the Supply tab, Avg2015 from the Avg2015 tab and.. you guessed it.. Avg2016 from the Avg2016 tab

However they only need to be loaded into the Order Tab IF the SKU is present on both sheets and it needs to ignore SKU's that arent present on the orders.


I did some google-ing and came across the following formula:

='Supply'! IF(A1-A9999='Order')

I am quite new when it comes to excel and this isnt working sadly.
Can anyone help me out?

[JohnTopley]

Post a small sample Excel file.

Click "Go Advanced" then "Manage Attachments" to upload a file

[Roofpigeon]

Hi thanks for your reply.

I made an example file.

so basically if the SKU is not present on the order tab, it shouldnt load it in from the other tab

[JohnTopley]

These will return data from appropriate sheet where SKUs match or blank if not matched.

=IFERROR(VLOOKUP($A3,Supply!$A$3:$B$29,2,0),"") Supply

=IFERROR(VLOOKUP($A3,Ave2015!$A$3:$B$29,2,0),"") Avg2015

=IFERROR(VLOOKUP($A3,Ave2016!$A$3:$B$29,2,0),"") Avg2016

Copy formulas down

Is this what you require?

[Roofpigeon]

Hi thanks again for your reply. I've been trying your code but I'm getting an error.

so I've been trying to break down the formula to understand it for future reference

=IFERROR = Returns a value you specify if a formula evaluates to an error
so VLOOKUP check in vertical columns
$A3 is naturally the SKU as a base value
then Supply!$A$3:$B$29 defines the A3 to B29 cells in the supply tab.

I dont understand the ,2,0 though, but if i remove it it states too few values defined.

the ,"" is if its not present it wont show I'm assuming?

[JohnTopley]

=IFERROR(VLOOKUP($A3,Ave2016!$A$3:$B$29,2,0),"")

the "2" refers to the (relative) column in the range: so we have two columns A & B so we match A3 against column A and get the result from column B (column 2)

"0" says get exact match,

and "" is the error condition (null) but equally could be "SKU not found"

=IFERROR(VLOOKUP($A3,Ave2016!$A$3:$B$29,2,0),"SKU not found")