Formula needed to identify YES and return the value in the cell next to it

saraapple

Board Regular
Joined
Feb 3, 2020
Messages
165
Office Version
  1. 2010
Platform
  1. Windows
I have some very poor quality data that I need to extract a single reference from. This bit I have done by applying 7 different =MID formulas and now I have a table that looks like this:

Book1
ABCDEFGHIJKLMNO
1#1#2#3#4#5#6#7MATCH
2131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO2
3131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO2
4131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO2
5e : C2NOC273048YES48 : 01NO01/01/2NOto 31/0NO1/22 toNOCRD Hire NO4
6e : C2NOC273048YES48 : 01NO01/02/2NOto 28/0NO2/22 toNOCRD Hire NO4
7e : C2NOC273048YES48 : 01NO01/03/2NOto 18/0NO3/22 toNOCRD Hire NO4
8in FeeNOee : C2NOC273048YES48 : 01NO1/22 toNO 01/01/NOCRD AdminNO6
9in FeeNOee : C2NOC273048YES48 : 01NO2/22 toNO 01/02/NOCRD AdminNO6
10in FeeNOee : C2NOC273048YES48 : 01NO3/22 toNO 01/03/NOCRD AdminNO6
11in FeeNOee : C2NOC286911YES11 : 01NO2/22 toNO 01/02/NOCRD AdminNO6
12in FeeNOee StubNOub : C2NOC273048YES 20/12/NO048 : 2NOCRD AdminNO8
13in FeeNOee StubNOub : C2NOC273048YES 20/12/NO048 : 2NOCRD AdminNO8
14oc : CNO C45575NO752 : 2NO 26/02/NO : RYDENO02/22 :NOCRD AdhocYES14
15oc : CNO C46214NO142 : 2NO 26/02/NO : RYDENO02/22 :NOCRD AdhocYES14
16L CRD NOD AdminNOin Fee NOe Stub NOC273048YESub : C2NOREVERSAL NO10
17L CRD NOD AdminNOin Fee NOe Stub NOC349902YESub : C3NOREVERSAL NO10
18L CRD NOD Hire NOe Stub NOb : C27NO48 : 20NOC273048YESREVERSAL NO12
19L CRD NOD Hire NOe Stub NOb : C34NO02 : 20NOC349902YESREVERSAL NO12
Sheet1
Cell Formulas
RangeFormula
O2:O19O2=MATCH("yes",A2:N2,0)


What I need to do is locate the YES which I have done with a simple match formula so I know the column number BUT then I want to return the value from the cell to the left of the YES (this is the correct reference)
So for row two Yes is in column 2, I want it to return me the value from column 1 so the desired result I want the formula to return on row 2 is 131661

Can this be done?
Any help would be greatly apprected.
Sara
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
MrExcelPlayground12.xlsx
ABCDEFGHIJKLMNOP
1#1#2#3#4#5#6#7MATCH
2131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO2131661
3131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO2131661
4131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO2131661
5e : C2NOC273048YES48:01:00NO1/1/2002NOto 31/0NO1/22 toNOCRD Hire NO4C273048
6e : C2NOC273048YES48:01:00NO1/2/2002NOto 28/0NO2/22 toNOCRD Hire NO4C273048
7e : C2NOC273048YES48:01:00NO1/3/2002NOto 18/0NO3/22 toNOCRD Hire NO4C273048
8in FeeNOee : C2NOC273048YES48:01:00NO1/22 toNO 01/01/NOCRD AdminNO6C273048
9in FeeNOee : C2NOC273048YES48:01:00NO2/22 toNO 01/02/NOCRD AdminNO6C273048
10in FeeNOee : C2NOC273048YES48:01:00NO3/22 toNO 01/03/NOCRD AdminNO6C273048
11in FeeNOee : C2NOC286911YES11:01NO2/22 toNO 01/02/NOCRD AdminNO6C286911
12in FeeNOee StubNOub : C2NOC273048YES 20/12/NO048 : 2NOCRD AdminNO8C273048
13in FeeNOee StubNOub : C2NOC273048YES 20/12/NO048 : 2NOCRD AdminNO8C273048
14oc : CNO C45575NO752:02:00NO 26/02/NO : RYDENO02/22 :NOCRD AdhocYES14CRD Adhoc
15oc : CNO C46214NO142:02:00NO 26/02/NO : RYDENO02/22 :NOCRD AdhocYES14CRD Adhoc
16L CRD NOD AdminNOin Fee NOe Stub NOC273048YESub : C2NOREVERSAL NO10C273048
17L CRD NOD AdminNOin Fee NOe Stub NOC349902YESub : C3NOREVERSAL NO10C349902
18L CRD NOD Hire NOe Stub NOb : C27NO48:20:00NOC273048YESREVERSAL NO12C273048
19L CRD NOD Hire NOe Stub NOb : C34NO2:20NOC349902YESREVERSAL NO12C349902
Sheet5
Cell Formulas
RangeFormula
O2:O19O2=MATCH("yes",A2:N2,0)
P2:P19P2=INDEX(A2:M2,1,MATCH("YES",B2:N2,0))
 
Upvote 0
Solution
How about
Fluff.xlsm
ABCDEFGHIJKLMNO
1#1#2#3#4#5#6#7MATCH
2131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO131661
3131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO131661
4131661YES61_CartNOrtwrighNOght TriNOxie 13.NOTri AxiNO952570_13NO131661
5e : C2NOC273048YES48:01:00NO01/01/2002NOto 31/0NO1/22 toNOCRD Hire NOC273048
6e : C2NOC273048YES48:01:00NO01/02/2002NOto 28/0NO2/22 toNOCRD Hire NOC273048
7e : C2NOC273048YES48:01:00NO01/03/2002NOto 18/0NO3/22 toNOCRD Hire NOC273048
8in FeeNOee : C2NOC273048YES48:01:00NO1/22 toNO 01/01/NOCRD AdminNOC273048
9in FeeNOee : C2NOC273048YES48:01:00NO2/22 toNO 01/02/NOCRD AdminNOC273048
10in FeeNOee : C2NOC273048YES48:01:00NO3/22 toNO 01/03/NOCRD AdminNOC273048
11in FeeNOee : C2NOC286911YES11:01NO2/22 toNO 01/02/NOCRD AdminNOC286911
12in FeeNOee StubNOub : C2NOC273048YES 20/12/NO048 : 2NOCRD AdminNOC273048
13in FeeNOee StubNOub : C2NOC273048YES 20/12/NO048 : 2NOCRD AdminNOC273048
14oc : CNO C45575NO752:02:00NO 26/02/NO : RYDENO02/22 :NOCRD AdhocYESCRD Adhoc
15oc : CNO C46214NO142:02:00NO 26/02/NO : RYDENO02/22 :NOCRD AdhocYESCRD Adhoc
16L CRD NOD AdminNOin Fee NOe Stub NOC273048YESub : C2NOREVERSAL NOC273048
17L CRD NOD AdminNOin Fee NOe Stub NOC349902YESub : C3NOREVERSAL NOC349902
18L CRD NOD Hire NOe Stub NOb : C27NO48:20:00NOC273048YESREVERSAL NOC273048
19L CRD NOD Hire NOe Stub NOb : C34NO02:20NOC349902YESREVERSAL NOC349902
Daily
Cell Formulas
RangeFormula
O2:O19O2=INDEX(A2:M2,MATCH("yes",B2:N2,0))
 
Upvote 0
Thank you both that is perfect and I understand how the formula works and have applied to my work book :)
This will save so many hours work
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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