Return a value from another column and sheet using MATCH

0
votes

I have a worksheet where I need to fill in data referenced from other worksheets.

I need to match a value on my main sheet with one on another sheet and once the cell has been found I need to get the value of a different cell offset by 1 row up and 5 columns left. I know which columns I need but I need MATCH to return the initial row.

MATCH will give me the initial row that I need.
ADDRESS means I can build the cell reference.
OFFSET allows me to reference another cell if I have the original cell address.

However the ADDRESS function will only return the address as a text string and not as a reference that can be used in the OFFSET formula.

Does anyone know of a way to make this work?

Kind regards

Matt

1
excelexcel-formula
Using match does work for this, if I have followed your question correctly, but post an example of source data and exactly what you want to achieve. – Solar Mike
put the address within an indirect(), indirect converts a text string to a real cell reference if that is what you are getting at. – Ricards Porins
Hi Ricards. indirect worked as well as Darrens's below. You understood what I was getting at. – Matt Bartlett

1 Answers

2
votes

If the value on your main sheet is in cell Sheet1!A1, and the matching value is somewhere in column Sheet2!J:J.

The MATCH formula will return the row number:
=MATCH($A$1,Sheet2!$J:$J,0)

You want one row up from this so the formula will be:
=MATCH($A$1,Sheet2!$J:$J,0)-1
Make sure this does not fall below 1

To return a reference to another cell you'd use INDEX as this is non-volatile and won't try and recalculate unless you change a figure it uses.
It returns a reference to a cell given a range of cells, a row number and a column number.
Your column is always five columns to the left, so if you're matching in column J you want a figure from column E.

e.g. If you want row 5 from column E you'd use =INDEX($E:$E,5).

Replace the row number with your Match formula and you get the figure you're after:
=INDEX($E:$E,MATCH($A$1,Sheet2!$J:$J,0)-1)