Returning Unique Value Combined with an IF or Match statement

[Contigo92]

Hello,

I am looking for help with a formula to return a unique value once Excel has found a match. I will try and explain as best as I can.

I have a column (Column A) in a master worksheet (Worksheet A) that compares two columns from two separate worksheets (Worksheet B and Worksheet C).
Current formula I am using for this:

=IFERROR(IF(MATCH('worksheetB’!I3,'worksheetC'!$E$3:$E$400,0),"Y",),"N")

In Column A Workbook A, when Excel finds a match, it returns a Y , and when it doesn’t it returns an N. This works fine.

However, In the next column over on Worksheet A (Column B), I want Excel to return the specific reference number from Worksheet C, Column A from the corresponding row in which it found a match.
So if for example Excel finds the match in E14, is it possible to return the value in Column A in Row 14 back to Column B in Worksheet A?

Just to make it even more confusing, ideally Excel would only implement this formula if there was a Y value in Column A Worksheet A (arrived at from the above formula).

Essentially, I am trying to find a formula that says ‘if there is a Y in Column A, then Excel will return the unique ref code from where it found the match.’

I have tried several different formulas with no luck and I really don’t even know where to begin!

Any help would be greatly appreciated. If anyone needs additional info, just let me know.

Thanks!

 

[Haluk]

Can you try this;

=INDIRECT("WorksheetC!A" & MATCH(LOOKUP(WorksheetB!I3,WorksheetC!E$3:E$300),WorksheetC!E$3:E$300,0)+2)

 

[Contigo92]

Unfortunately it didn’t work. Thank you for your help though

 

[Shweta]

Essentially, I am trying to find a formula that says ‘if there is a Y in Column A, then Excel will return the unique ref code from where it found the match.’
!

I guess you are looking for the cell address where it found the match. In that case below formula should work.

=ADDRESS(MATCH(WorksheetB!I3,WorksheetC!$E$3:$E$400,0),1)

So if for example Excel finds the match in E14, is it possible to return the value in Column A in Row 14

And if you want to fetch the value from column A of Worksheet C where it found the match then try the below formula:

=INDIRECT("WorksheetC!"&ADDRESS(MATCH(WorksheetB!I3,WorksheetC!$E$3:$E$400,0),1))

 

[Aladin Akyurek]

Try to post samples along with the desired output in support of your verbal description.