Lookup, VLookup, HLookup

First, let me thank in advance. I have had great answers when posting.
Here is what I want to do:

I want Excel to look on the IRR sheet, find Department 10, find the cell that has Curr YR Current Month Purchases – Retail, and put that number on the TLE sheet in cell C3.

The information on the IRR sheet will not be in the same order all the time, that’s why I want it to it this way.

I have attached what I am working on.

This what I am using in all of the cells. But if the IRR sheet changes (starts on row 3 instead of 43), none of the formulas work.

='IRR'!E232

Just like the formulas you have suggested, if the IRR sheet changes, the formulas don't work.

Am I asking Excel to do something that it won't do?

Thanks to both of you for your help.
protonLeah, I can get your formula to work on the first column of numbers, but forgive me, I am very lost with this type of formula. Would it be too much to ask if you could explain the formula to me?

Originally Posted by protonLeah

Starting with the inner function, MATCH(A3,IRR!$C:$C,0), looks up the value 10 from cell A3 on TLE and finds it in column C of IRR sheet and returns the ROW where it is found. Match finds 10 at row 71 on IRR and passes it to the OFFSET function>

OFFSET(IRR!$C1,MATCH(A3,IRR!$C:$C,0)-1,1,4,2) ----> OFFSET(IRR!$C1,71-1,1,4,2)

That Row number is used in the OFFSET function to define a range or block of cells at a specified position from an anchor cell. In the present case, the anchor cell is C1 on the IRR sheet. The 10 is found at row 71 which is 70 rows below C1 (so I substracted 1).

Next it moves over 1 column to D71. The size of the desired range is specified as 4 rows high and 2 columns wide, with D71 as the upper left corner: i.e. D71:E74.

Finally, VLOOKUP, is used to find "Curr YR" in that range (second row) and return the value in column 2 of the block vis. $63,772.

WOW! Thank you very much. I have a lot of learning to do.

protonLeah,
One more question please.

How do I get rid of the #N/A