I am looking for help in finding a string within a workbook and returning the sheet name (Address) or where the same can be found, e.g. Let's look for Lisbon which is located in sheet Portugal (sheet 5 of 10)

Lookup string "Lisbon" ... Looking all sheets within a workbook and would return... Sheet "Portugal!A2"

I do have a formula which lookup through all sheets but don't tell me the location, so any help will be appreciated.

Kind regards,

  • 2
    I'm not familiar with looking up strings across multiple sheets. However, I'm aware there are some functions that will return a cell's location reference. Perhaps if you can provide the formula you're starting from, I could mash it together with what I know to give you something workable? – Iszi Sep 4 '13 at 17:12

I can give you a more specific answer if you provide the way in which you are looking up across multiple sheets, however I can provide some guidance in that you may want to look into the INDIRECT funtion. It allows you to take a value as text and look up the cell that it references. It is in the form INDIRECT(,[reference style])

Where Cell reference can be something as Simple as

  • A1 to mean Column A Row 1 or
  • R1C2 to mean Row1 Column1

Or something More Complicated like

  • CONCATENATE("'October SM Cards'!B",'2C2FWorksheet'!A1+34)

Where I an Concatenating

'October SM Cards'!B



resolving to the Value of A1 +34 on the 2C2FWorksheet before it appends to the 'October SM Cards'!B creating a reference like

'October SM Cards'!B37

when everything is done. Wrap that in an INDIRECT and it will reference the cell name/number you have pieced together.

Complete reference example

=INDIRECT(CONCATENATE("'October SM Cards'!B",'2C2FWorksheet'!A1+34))

With all of this information perhaps you can piece together a location by using the sheet name

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.