Lookup formula

[Sunshine8790]

So I have a multi-tab spreadsheet, and each sheet has hundreds of rows of data along with many columns of data.
Data is private so cannot share, and will do my best to explain what I need:
On one tab (we'll call it Sheet1), I have this formula down column D:

=MATCH(A1,'Emp Summary'!A:A,0)

This either brings up the row number that the data in Column A is found in the Emp Summary tab, or an #N/A error. If I see the error, I know I need to add that information to the other tab it's currently not found in.
HOWEVER....
In column E, I have this formula currently:

=MATCH(A1,'Main Sheet'!A:A,0)

Emp Summary only has current data (it's updated frequently with new data)
Main Sheet tab has ALL existing data.

What I need is a to update my formula in column E. I still need it to find a match, if it exists, but instead of returning the line number it's found on in the "Main Sheet" tab - I need it to return the text in Column H of the line number it's found on, or #N/A if no match.

I hope that makes sense and that someone can help me.

 

[Joe4]

Since you are using Excel 365, I think you can use the XLOOKUP function to do what you want.
See here: XLOOKUP function - Microsoft Support

Something like this:

=XLOOKUP(A1,'Main Sheet'!A:A,'Main Sheet'!H:H,"N/A")

 

[RoryA]

That sounds like:

=INDEX('Main Sheet'!H:H,MATCH(A1,'Main Sheet'!A:A,0))

 

[Sunshine8790]

Thank you both!
Both formulas work perfectly.

 

[Joe4]

You are welcome.
Glad we could help!