Append numberical returned value to cell location

**umang2382** · 06-24-2015, 05:07 PM

Hi All,

Hopefully this is a simple problem, but I can't seem to find its solution.

I have a formula:
=MATCH(D28,Sheet1!A$1:Sheet1!A$200,0)
that returns a value.
I'd like to expand the formula to take this value and perform a cell lookup and copy the cells contents. As example, if the returned value is 131, I'd like to reference cell $J131 (in the same formula). The '131' should change with whatever value is returned by the match formula.

Any help would be much appreciated.

**FDibbins** · 06-24-2015, 05:16 PM

Hi, welcome to the forum

Match does not exactly return a "value", it returns a position in a range. So if that is returning 131, then it is saying it found your search criteria (the contents of D28) in row 131 - your range starts in row 1.

If you want to return the contents of a specific colum/row intercept, you need to add INDEX() to that....
=INDEX(Sheet1!J$1:Sheet1!J$200,MATCH(D28,Sheet1!A$1:Sheet1!A$200,0))

**Tony Valko** · 06-24-2015, 05:52 PM

Originally Posted by FDibbins

If you want to return the contents of a specific colum/row intercept, you need to add INDEX() to that....

=INDEX(Sheet1!J$1:Sheet1!J$200,MATCH(D28,Sheet1!A$1:Sheet1!A$200,0))

No need to repeat the sheet name:

=INDEX(Sheet1!J$1:J$200,MATCH(D28,Sheet1!A$1:A$200,0))

The MATCH function returns the RELATIVE position of the lookup value within the lookup array.

Consider this example...

Data Range

	A	B	C	D
9	Value		Lookup	54
10	5		Location	6
11	23	------	------	------
12	29
13	41
14	43
15	54
16	60
17	71
18	74
19	91
20	95

This formula entered in D10:

=MATCH(D9,A10:A20,0)

MATCH returns 6 because the lookup value 54 is found at the 6th position RELATIVE to the lookup array (range A10:A20).