CODEWITHSUNDEEP
excelexcel-formula
Matt Bartlett
Matt Bartlett

Reputation: 360

Return a value from another column and sheet using MATCH

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

Upvotes: 0

Views: 8295

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19757

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)

Upvotes: 2

Related Questions