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.
=IFERROR(INDEX(IRR!$E$43:$N$110,MATCH(TLE!$A$3,IRR!$C$43:$C$110,0),MATCH(TLE!C$2,IRR!$E$41:$N$41,0)),"")Originally Posted by jmcole
Copy down and accross.
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
Thanks RobertMika, but I couldn't get this to work at all.
ProtonLeah,
This works, but the information on the IRR sheet will not be in the same order all the time. It might start on line 3 instead of line 43. Can this be arranged that way?
Last edited by jmcole; 09-27-2013 at 03:52 PM.
Maybe:
=VLOOKUP("Curr YR",OFFSET(IRR!$C1,MATCH(A3,IRR!$C:$C,0)-1,1,4,2),2,FALSE)
Ben Van Johnson
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?
https://www.dropbox.com/s/cto40pzzfkeu0cg/Test.xls
For some reason I can not upload workbook thru forum.
Some of your heading do not match.
Have a look at them.
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?
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.
Thank You again.
Last edited by jmcole; 10-04-2013 at 04:10 PM. Reason: solved
protonLeah,
One more question please.
How do I get rid of the #N/A
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks