Find text in another worksheet and bring value from two cells to right of text.

I am looking for a formula that will search a worksheet for text. When it finds the text the value in the cell 2 columns to right of the text will be result of the formula.

It's kind of like a VLOOKUP up the text won't always be in the same column so I can't use an array as it wouldn't be consistent.

I have tried this:


=INDEX(John Smith!A1:U425,SUMPRODUCT(--(John Smith!A1:U425=Accounts Reporting 2015!A139)*ROW(John Smith!A1:U425)),SUMPRODUCT(--(John Smith!A1:U425=Accounts Reporting 2015!A139)*COLUMN(John Smith!A1:U425))+3)

John Smith the tab where the text will be and Accounts Reporting 2015 is where I want the value to appear. This isn't working though as I keeping getting an update values prompt which I don't understand. The workbook isn't linked to any other workbooks or external data. When I click cancel on the update values prompt I get a #NAME? error.

Sorry 3 columns to the right not two.

As you have a space in the sheet name, you will need to enclose it within apostrophes, like this:

=INDEX('John Smith'!A1:U425, ...

and the same for all the other occurrences.

Hope this helps.

Pete

Pete,

I have been pulling my hair out for ages.

That has solved it. Thank you so much.

Dec

Glad to be able to help, Dec.

As well as marking the thread as SOLVED., you might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete