Combining IF/ LOOKUP - Newbie to formula's

[OldBoy]

New to using Excel formula's and am trying to create an IF/LOOKUP formula to look at multiple sheets within a workbook and display the information within the 'compare' sheet.

The yellow cells are where data will be entered.

What I am trying to achieve:
Type the store numbers on the compare sheet (B2 and D2). For the sheet attached I have put 190 (in B2) and 2012 (in D2)
B2 store shows the predicted and actual values of 190 in columns B & C
D2 store shows the predicted and actual values of 2012 in columns E & F
(No need to worry about variance and difference columns)

So, if I change the store numbers in B2 and D2 to any of the sheet numbers, I want it to display the correct info for that particular store within the compare sheet.

I have attempted a formula, which you can see... I have basically looked at some previous sheets that had IF and LOOKUP on it and tried to replicate that for my sheet, but with no luck. No one at work is able to help, so hopefully you wonderful ladies and gents can possibly help.

I hope I have explained it clearly, if not please let me know and I will try and elaborate.

Thank you in advanced for helping this formula newbie.

[Jacc]

You mean like this?

[OldBoy]

Brilliant!!!

Thank you so much! I guess I just need to copy and paste so it fills the rest of the column?

I have never seen that kind of formula before, will definitely be looking to dissect and understand what ya did, unless you are able/willing to give a quick explanation? If not, that is perfectly fine, thank you once again

[Jacc]

Thanks!

Yup, just copy paste. Or rather just select one cell with a formula, grab it by the lower right corner and drag down.

First of all, there is no need to LOOKUP anything, you just want to look at the same rows of data from a different sheet. Since your sheets are all the same (same columns used) it is just a matter of using the = sign.
As a general example, putting this formula in A1 in a fictive sheet:

Please Login or Register  to view this content.

would just give you the value from A1 in Sheet2. Copying down would give you all the rows you wanted.

I did the same thing but instead of providing a fixed sheet reference, I'm building up the reference with the contents of your two input cells. The & operators are for concatenating values or strings. The string parts has to be surrounded by "". The INDIRECT function evaluates the string that I built up and converts it into a real reference. Under the Formulas banner there is a button called Evaluate Formula. Select a single cell with a formula and hit that button to see the formula calculate, step by step.