Mr Excel & excelisfun Trick 35: 3 Way Lookup INDEX or OFFSET function?

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

A B C D
1. 3/31/11 90 4/2/11
2. 4/1/11 91
3. 4/2/11 92
4. 4/3/11 93
5. 4/4/11 94
6. 4/5/11 95
7. 4/6/11 96
8. 4/7/11 97
9. 4/8/11 98


I am using index and match function to look up a values that correspond to the date entered in C1.
So in D1, I have this formula = index($B$1:$B$15,Match(C1,$A$1:$A$15,0)) then I get 92 in this example. I would like to use the offset function to off set the row by 1 so when I input 4/2/11 in C1, I get 91 in D1 instead of D2.

I tried using =INDEX($B$1:$B$15,MATCH(OFFSET(D6,-1,0),$A$1:$A$15,0)) but I get a #N/A error for some reason. Any ideas on what's wrong with the formula I have?

I am needing to find a formula that will bring back a number to a table from another worksheet in the workbook. The issue that I have is that the number is 8 columns over and 1 row down from the matching field and so I can't use a VLookup. So for TECH1 I need it to return 1188. for TECH2 I need 594 and for TECH3 it should be 803.

Company/Name Service Schedule Tech 1 Sales 1 Start Date Annual Smith2 RESPC SIGNATURE EOMJAN3THU TECH1 Sales1 2/15/2011 534 Smith2 RESPC SIGNATURE EOMJAN1MON TECH1 Sales1 2/8/2011 654
TECH1 Setups: 2 1188 Company/Name Service Schedule Tech 1 Sales 1 Start Date Annual Smith ST SIGNATURE EOMJAN1FRI TECH2 SALES2 2/11/2011 594 TECH2 Setups: 1 594 Company/Name Service Schedule Tech 1 Sales 1 Start Date Annual Adams LN MOLE EOMFEB1FRI TECH3 SALES3 2/4/2011 390 Adams LAWN SILVER TECH3 SALES3 2/21/2011 0 Adams LN RD 1 A-MAR1MON TECH3 SALES3 2/21/2011 59 Adams LN RD 2 A-APR1MON TECH3 SALES3 2/21/2011 59 Adams LN RD 3 A-MAY1MON TECH3 SALES3 2/21/2011 59 Adams LN RD 4 A-JUN1MON TECH3 SALES3 2/21/2011 118 Adams LN RD 5 A-JUL1MON TECH3 SALES3 2/21/2011 59 Adams LN RD 7 A-NOV1MON TECH3 SALES3 2/21/2011 59 TECH3 Setups: 8 803

I am having trouble performing a lookup function. I've tried a few
suggestions I've found by searching the user groups, and cannot seem to
get one to work for me.

I have an array consisting of dates (column 1) and prices (columns 2
and 3 below, but many columns in my sheet. Below the array are some
basic descriptor statistics, including max and min. I'd like a lookup
function that tells me, for each column, what date corresponds to the
max and min, as in cells F2:G3, below.

Any help would be greatly appreciated. Thanks in advance,
Michael


1 2 3
A Jan-04 $4 $2
B Feb-04 $1 $8
C Mar-O4 $5 $3
D Min $1 $2
E Max $5 $8
F mindate 02/04 01/04
G maxdate 03/04 02/04

Hello people....

I am having a heck of a time coming up with the proper combination of functions to return data to my worksheet.

Attached, you will find a screen shot of the pages I am dealing with.
Here is what I am trying to do:

On the page named Update Master, I am trying to put formulae into the highlighted cells C2:C11. Each cell will contain a formula to MATCH the team name in UpdateMaster!A:A with a team name in the chart found on worksheet WEEK ONE. Then, I want to return the value found 8 cells down, and 1 cell to the right of that MATCH. Here is a specific example:

In cell Update Master!C2, I need a formula that will MATCH the value in Update Master!A2, , with a value on the WEEK ONE! worksheet. (In this case, 2EZ. After locating the match, I want to return the value found in the bottom right of that players weekly score box. (WEEK ONE!C9...in this example).

I can't make a direct reference to the data...because the value in Update Master!A:A will change after each week. The team names will be sorted in alphabetical order after being sorted by the value found in Update Master!AE:AE...(each player's accumulated score).

I have tried so many combinations of MATCH, LOOKUP, OFFSET, INDEX, ROW...etc., and I keep getting VALUE# or NA# errors at some point in the computation. Maybe one of you can point me in the right direction.

Thanks, in advance for any help you might be able to provide.

Hi

I want to find the last time a value (letter "N") appears in a row, then pick up the value in the cell to the left of my last value in the same row.

Column A is where I want my new formula
Column B is the name of a unit
Column C is the date of a visit
Column D tells me what type of visit (F,N or H)
Column E is the date of a visit
Column F tells me what type of visit (F,N or H)
and so on.......

On the row it reads
Missing formula! - Andover - 15/9/08 - N - 18/9/08 - F - 25/10/08 - N
Missing Formula! - Barnsley - 18/9/08 - F - 25/9/08 - F - 1/10/08 - H

I want to find when the last N visit to Andover took place and then subtract it from todays date so I can then data sort my list of sites in column A to see how long since the last N visit to Andover. I am fine with the subtraction from todays date, it is just identifying the right bloomin' date.

I have used =LOOKUP(2,1/(A3:A10="N"),A3:A10 which gives me the last N but not the cell to the left of it. Offset does not seem to work

Also used =OFFSET(INDEX(A3:A10,MATCH("N",A3:A10,-1))0,-1) which gets me to the right cell until two N's appear on the same row or H appears.

The next part of the problem is that Index seems to see "N" as a value, thus if you put a "H" into the row it sees it as a value less than "N" (higher in the alphabet) and so counts it into the equation, if you put an "X" in it ignores it.

I am aware my layout could improve, but the data sort is the key to the spreadsheet.

Cheers

SpoonMcr

i currently have multiple dynamic named ranges in my file all defined by the following formula (or similar to):

=OFFSET($B$4,0,0,COUNTA($B:$B),COUNTA($4:$4))

the problem as you all know is that this is a dynamic function and therefore it is impacting the speed of my workbook tremendously. through searching other posts i have found out that i can define the ranges of rows and columns by using formulas similar to this:

=MATCH(REPT("z",255),Sheet1!$D:$D) (for rows)
=MATCH(REPT("z",255),Sheet2!$2:$2) (for columns)

I am just having a hard time putting those together to define a range. Can anyone out there lend some insight? Thanks for the help!

Gex

Please help.

My data looks like this:
A2:A6 = Account 1,2,3,4,5.
B1:F1 = Monday 1st Jan to Friday 5th Jan.
In between this data are random balances of $2000, $0, or nothing (empty cell). eg. Account 1 has balances of the following for its week:
-,2000,0,-,2000 (for Monday-Friday).

My request: I need to report the accounts that have been imbalanced for 3 days or more.
In more detail, how can I work out per account (1.) the current balance; (2.) the dates the current balance appeared, and (3.) the date that the last 0 appeared? I have used all manner of offset, match, index, lookup functions to work this out, but little luck so far.
I have worked out the current balance with this formula:
=LOOKUP(MAX(B2:F2)+1,B2:F2)The dates though, are proving difficult.
Any tips?

I need to place a formula in N13, that will add the values 12 columns to the right of X13 and the X number of columns to the right based on the Value in cell S6 -1

See sample data using sum formula

Your assistance will be most appreciated

******** ******************** ************************************************************************> Microsoft Excel - Branch A.xls ___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout T9 N13 T13 T15 AI15 AJ15 AK15 AL15 Q16 T16 T17 Q19 T19 X19 Y19 Z19 AA19 AB19 AC19 AI19 AJ19 AK19 AL19 Q21 T21 X21 Y21 Z21 AA21 AB21 AC21 AI21 AJ21 AK21 AL21 Q22 T22 Q24 T24 X24 Y24 Z24 AA24 AB24 AC24 AI24 AJ24 AK24 AL24 =
N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM 9   | | 10/01 | | P/YEAR AVE |  00/01 YTD |   09/12   09/11   09/10   09/09   09/08   09/07   09/06   09/05   09/04   09/03   09/02   09/01   08/12   08/11   08/10   09/08 10   | | - | | - | - | - - - - - - - - - - - - - - - - 11   | |   | |   |   |                                 12   | |   | |   |   |                                 13 32  | | 7  | |  
| | 4  3  6  8  5  6  0  0  0  0  0  5  7  11  9  0  14   | |   | |   |   |                                 15   | | 83,000  | |  
| | 65,000  45,000  75,000  92,000  68,000  72,000  0  0  0  0  0  77,500  108,500  170,500  139,500  0  16   | | 0  | |  
| | 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  17   | | 17,500  | |  
| | 21,000  0  0  18,000  0  12,000  0  0  0  0  0  0  18,000  0  12,000  0  18   | |   | |   |   |                                 19   | | 100,500  | |  
| | 86,000  45,000  75,000  110,000  68,000  84,000  0  0  0  0  0  77,500  126,500  170,500  151,500  0  20   | |   | |   |   |                                 21   | | 74,700  | |  
| | 58,500  40,500  67,500  82,800  61,200  64,800  0  0  0  0  0  69,750  97,650  153,450  125,550  0  22   | | 0  | |  
| | 0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  23   | | - | | - | - | - - - - - - - - - - - - - - - - 24 = | | 25,800  | |  
| | 27,500  4,500  7,500  27,200  6,800  19,200  0  0  0  0  0  7,750  28,850  17,050  25,950  0  25   | | - | | - | - | - - - - - - - - - - - - - - - - Sheet1  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.