How to use a cell address from a formula in another formula

[LonesomeJoe]

I have a spreadsheet which has two columns of data and a formula, "IF(ISERROR(MATCH(V3,Z:Z,0)),"Not Found",ADDRESS(MATCH(V3,Z:Z,0),26,4))" which tests for a match between the cell in Column "V" and the data in Column "Z" and if a match is found, returns the cell address in Column "Z" of the match. I need to take some data from another cell in the same row that the match is in and put it in a cell in the same row as the original data. For example:

the data in Cell "V3" has a match in Cell "Z555". I need to take data from Cell "Y555" and put it in Cell "X3"
the data in Cell "V6" has a match in Cell "Z1117". I need to take the data in Cell "Y1117" and put it in Cell "X6"

Can I do this with a formula? I'm using Excel 2003. Thanx.

[jcaynes]

I believe this is resolved with an index(array of data(match(V3,Z:Z,0),column number for column Y in array). Control, shift and enter simultaneously to activate the array formula.

[6StringJazzer]

Put this formula in X3 and copy down:

Please Login or Register  to view this content.

I think you are misunderstanding how ADDRESS works. In your example, the result of the formula for row 3 will be Z555. But that result is pretty much useless. You need the value from that cell.

[LonesomeJoe]

That's the magic formula! Thank you!

[6StringJazzer]

You're welcome! Thanks for the rep, and thanks for marking your thread Solved!