Returning Unique Value Combined with an IF or Match statement

Contigo92

New Member
Joined
Nov 15, 2017
Messages
2
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Can you try this;

Code:
=INDIRECT("WorksheetC!A" & MATCH(LOOKUP(WorksheetB!I3,WorksheetC!E$3:E$300),WorksheetC!E$3:E$300,0)+2)
 
Last edited:
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top