Matching a text reference in separate Excel sheets

[prssmzk]

I have been going crazy trying to write a statement that will allow me to find a text reference that is common to both spreadsheets but in different column, row numbers. Once I have found the cell I want to offset the column position by 17 and then copy the contents of that final cell back into the original spreadsheet.

What I tried was:
In spreadsheet1, at position cell R10 I typed the following ... =match("text in spreadsheet1", spreadsheet2!$E:E,0) 'this returned a number 14, saying that the text in spreadsheet1" was found in spreadsheet 2 at position cell E14.

so then I tried
=concatenate("U",R10) 'this then gave U14 as the new position of what I wanted the contents copied back into spreadsheet 1.

This is where I got stuck. How do I copy the contents of the cell U14 in spreadsheet 2 back into spreadsheet 1 at cell R10 (ie where I started from)?

Any help would be appreciated?

Cheers

[dilipandey]

Hi prssmzk,

Try below formula in any other cell except R10 to avoid circular reference.

=INDIRECT("sheet2!"&CONCATENATE("U",R10))

Regards,
DILIPandey
<click on below * if this helps>

[prssmzk]

Hi prssmzk,

Try below formula in any other cell except R10 to avoid circular reference.

=INDIRECT("sheet2!"&CONCATENATE("U",R10))

Regards,
DILIPandey
<click on below * if this helps>

what I should have done is to include example spreadsheets, which I have attached - to help explain what I was trying to do.

I hope I haven't confused it further?

[dilipandey]

Before checking even the sheets you attached.. I have to say that "Indirect" function does'nt work on closed workbooks. So if you applied indirect function in sheet1.xlsx to get data from sheet2.xlsx (and if sheet2.xlsx is closed at the moment), you'll not get results fetched out

Why I suggested Indirect function in my example is because you have used the term "sheet" in your query and sheet(s) are there in a workbook, so indirect function will work in a workbook and you can fetch data from any sheet, within workbook only.

Regards,
DILIPandey
<click on below * if this helps>

[prssmzk]

Thanks I hadn't realized the the difference between sheet and workbooks.

What I am working with is separate workbooks.

Any help you are able to provide is greatly appreciated.

[dilipandey]

Alternatively, you can use below formula in M3 of sheet 1 and drag towards right :-

=INDEX(sheet2.xlsx!$H$1:$J$11,MATCH($B3&$C3,sheet2.xlsx!$D$1:$D$11&sheet2.xlsx!$E$1:$E$11,0),COLUMN(A$1))

Regards,
DILIPandey
<click on below * if this helps>

[prssmzk]

Not sure what I am doing wrong. I pasted in the formula you provided into cell M3 - but I end up getting a #value! error message.

Are you able to attach your spreadsheet answer?

Many thanks

[dilipandey]

Okay... see the attached zip file where I have included both the workbooks

prssmzk.zip

Regards,
DILIPandey
<click on below * if this helps>

[prssmzk]

That is fantastic - you are a whiz.

What do the curly brackets mean - how do they come about - I guess they are a necessary part?

Regards

[dilipandey]

To get those curly brackets, enter formula using key combination ctrl shift enter and that's called array

cheers

Regards,
DILIPandey
<click on below * if this helps>