Offset Function error with asteresk

Hello,

I am trying to use offset and match together to create a lookup on a spreadsheet of unsorted numbers. The cells cannot be sorted due to the formatting that the cells are in. I am pasting the contents of the worksheet from another program into my reference worksheet (TH Att Det Master Rprt). Then I have an input sheet that searches for imputed text and cross references the reference sheet. The formula is as follows:

=OFFSET('TH Att Det Master Rprt'!$A$1,MATCH($D2,'TH Att Det Master Rprt'!A:A,0)*2,6,1,1).

The Value I am checking is in D2 of the originating sheet, and I am asking it to scan all of column A:A in the reference sheet to find the value in D2. I need it to offset a total of 5 cells down, but when I enter the offset value it will not allow me to enter anything greater than -1 without requiring an asterisk before it. Up until and including entering 1 the formula works as expected, moving down 1 cell per increment, but as soon as I enter in 2 (*2 really) it jumps to a random cell in the page. I cannot find out why it is doing this. Could someone help?

Dave

I do not want to do that... that is the problem! I did not know how to find out what cell the offset was going to... I thought it was 10 down, but when I altered that cell my offset result cell did not change. It apparently was interacting with a cell 3000 cells down!!! But, if I changed it from 2 to 1, it would interact with the appropriate cell (I checked this to be sure). I want to use the nested Match and Offset functions because I need it to find a value vertically and then from there use many versions of the formula to grab information offset up and right, down and right, and accross and right from the resulting cell that was 'matched'. I was told that with VLOOKUP and HLOOKUP I would be limited with my results because after finding the cell that I am looking for it would only be able to move in 1 direction to pull data, not two. IE:

Cell to be found : a1
Cells needed to be found with alternate formulas: b3, c8, d2, etc.

The only difference between what is above and my sheet is that I am looking over 8000 vertical cells and then moving between 5-10 cells up, down, and right to the data I need (6 alternate expressions of the formula are all being made; all have the same problem).

What makes this worse is that it seems to be a problem with nesting the formulas together... I have noticed that if I do a direct reference or type in the value I am looking for into the formula without the MATCH function that the formula works and excel does not try to enter in an asterisk, but when I use the MATCH formula everything goes to hell. I do not know another solution to the problem, and using the match formula has worked in the past.

Oh, another information tidbit: the cell D2 is pulling data off of the exact same formula going to another spreadsheet. It is working fine, but the spreadsheet I am trying to do is not.

Thanks for looking at this; I spent hours at work trying to find a solution myself before seeking superior intellects! (A chimps' gotta try, right :D)

Dave

Thanks for the post,

I am not sure if I am understanding this, but the index function will allow me to shift left or right a certain amount of cells, but I need to find a cell, say A10, and then pull the value B11, which is offset both right and down by 1. I do not think that I can do this with match.


A B C D E F
Ex 1 Data 1 Data 2 Data 3 Data 4 Data 5
blank Data 6 Data 7 Data 8 Data 9 Data 10
blank Data 11 Data 12 Data 13 Data 14 Data 15
blank Blank Blank Blank Blank Blank
Ex 2 Data 1 Data 2 Data 3 Data 4 Data 5
blank Data 6 Data 7 Data 8 Data 9 Data 10
blank Data 11 Data 12 Data 13 Data 14 Data 15
blank Blank Blank Blank Blank Blank

So in this Example I need it to scan through Column A, find Ex 2 (it is a reference #) and then search for Data 11, Data 13, and Data 15. Those require going both right and down, not just one or the other.

This info is being transfered to a sheet that looks like this:

Ex 1 Data 6 Data 12 Data 3 Data 15
Ex 8 Data 6 Data 12 Data 3 Data 15
Ex 4 Data 6 Data 12 Data 3 Data 15
Ex 2 Data 6 Data 12 Data 3 Data 15
Ex 3 Data 6 Data 12 Data 3 Data 15

The Examples are not sorted due to the blank cells between them (5-10 rows of blank cells between Ex 1 and Ex2, etc).


Previously, in the sheet below, I searched for Data 11in Column B, and then I would use offset to look up and to the left or right. This worked fine. As soon as I try and use the same formula to go downwards and to the right or left, however, I get an asterisk and an error.

A B C D E F
Ex 1 Data 1 Data 2 Data 3 Data 4 Data 5
blank Data 6 Data 7 Data 8 Data 9 Data 10
blank Data 11 Data 12 Data 13 Data 14 Data 15
blank Blank Blank Blank Blank Blank
Ex 2 Data 1 Data 2 Data 3 Data 4 Data 5
blank Data 6 Data 7 Data 8 Data 9 Data 10
blank Data 11 Data 12 Data 13 Data 14 Data 15
blank Blank Blank Blank Blank Blank


If using nested index will let me offset vertically and horizontally from a matched cell at the same time, then I think it will work. I just dont know how to do that. Thanks for taking the time to look at this. I hope I am describing my problem properly.

Dave

That last post almost brought a tear to my eye. Yeah, it worked. The F2 value was referencing a different value than needed on a different sheet, but when I looked at my comment it was because I was not clear. I figured out how to manipulate what you wrote out to give me the desired result, but I do not know the real idea behind the solution or how to replicate it again... so I have some studying to do!!!

Thank you again for all of your help,

David Sondergaard

Oh, do you know a place where there are good excel tutorials in progression from beginner to uber hacker?