Match and return across two workbooks

[Defaced]

I'm trying to figure out if what I want to do in my head will actually work and I am coming up short with VLOOKUps, Index/Match with Indirect and I'm at a loss.

I am simple trying to lookup what is in cell F1 on a sheet in one workbook across all tabs in another workbook (tab name is the same as F1) once it finds that tab it will return what is in cell M11. If it's not possible to lookup by tab name, the text that is in cell F1 is the same in both workbooks.

So if F1 in Last Name First Name, I want it to search the other workbook for that and return what's in cell M11. Is this possible?

 

[JonXL]

Okay... I solved the problem I was having and maybe it is the same as you are having:

I had two 'instances' of Excel running. I closed the workbook I wanted to reference and reopened it from the workbook I had the formula in (using Ctrl+O and navigating to the file).

This fixed the reference error and the formula works fine now.

 

[Defaced]

That was the exact same issue I had (using 2010 and two instances open). Closed the one and reopened and was able to pull in the 1234. Thank you both.