From cell in sheet 2 find name in sheet 1 and value offset

**Frank345** · 10-04-2008, 06:18 AM

I am using Excel 2003, Windowx XP Professional, and Internet Explorer 7

In one Excel 3003 example workbook sheet1 contains data copied in from Internet Explorer 7 that keeps changing position on the sheet. From a cell, EG C5, in sheet2 I want to use functions to find a name in Sheet1 and pull the value on the same row say seven columns to the right back into sheet2 cell C5.

My knowledge of Excel is very limited but this would seem to be a simple common thing to have to do, but have been unable figure it out. If this has been answered elsewhere I appologise, this is my first time to this forum which I found with google.

TIA

**rwgrietveld** · 10-04-2008, 06:28 AM

It seems that the copy-paste of yout IE does not give you the result you need. Please add/attach a workbook that contains the data from IE and on another sheet the result you like. We'll look for the right formula's/code to use.

**Frank345** · 10-04-2008, 07:06 AM

I don't see any attach icon to use to attach the wookbook to a reply?

**Frank345** · 10-04-2008, 07:54 AM

On example sheet2 cell I11 currently is using this function =+Sheet1!J15 to retrieve the value of BIR-T in Sheet1. The problem with this is that whenever an item is added or subtracted to sheet1 above the BIR-T entry the position of BIR-T and it's accompanying value move up or down accordingly causing the =+Sheet1!J15 function to retrieve the wrong value. The consequence is that every sheet that retrieves a value from sheet1 following a change is wrong and has to be corrected.

As there are many sheets accessing these values, it would be preferred to have a function that finds the BIR-T value on Sheet1 then retrieves the value seven columns over to the right of it. Then the sheet1 list would be changed at will without resulting in errors in the retrieving sheets.

**Frank345** · 10-04-2008, 08:04 AM

It seem the that size limit of 1000 kbs doesn't allow wookbook to load despite cutting it down, it seems to remain the same size. So thanks anyway.

**ratcat** · 10-05-2008, 12:50 AM

G'day Frank,

Have you try using the VLOOKUP formula.

http://www.contextures.com/xlFunctions02.html

HTH

**Frank345** · 10-05-2008, 01:40 AM

I solved the problem myself by nesting the MATCH function within the INDEX function.

An INDEX function returns a cell value of a specific position in a list or array.

The INDEX function has three arguments separated by commas and enclosed in parentheses:

INDEX( array, row number, column number)

That is: INDEX function will return the value in the array on the specified row in the specified column.

The problem is the row changes as new items are added to the list ahead of the item you want to reference or are deleted.

The solution to this problem is the MATCH function has two arguments and a variable separated by commas enclosed in parentheses:

MATCH( lookup value, lookup array, match type variable) { variable = 1, 0 or –1}

The match function returns the row in the array.

Therefore, by substituting the MATCH function in parentheses for the row number in the INDEX function, since the parentheses within parentheses are executed first, it provides the correct value for the INDEX function which is executed next so that it returns the correct relative cell value.

Additionally, by making the array value in the INDEX function as well as the lookup value and array value in the MATCH function absolute values, it is then possible to move the formula around on the worksheet without having its reference values change.

With two worksheets one of the final formulas on Sheet2 looked like this:

=INDEX(+Sheet1!$A$2:$J$200,(MATCH($A$11,Sheet1!$B$2:$B$200,1)),10)

I hope this solution and explanation is of help to someone else.

**Frank345** · 10-05-2008, 01:50 AM

Hi RatCat,

Thanks for trying to help. When I last checked the forum there had been no other new posts, so I decided to take another crack at the problem my self and came up with something that seems to work. But, I will have a look at your suggestion to see if it is a better solution. Thanks very much for your suggestion it is much appreciated.

Regards,
Frank345