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:
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
| Book1 | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | #1 | #2 | #3 | #4 | #5 | #6 | #7 | MATCH | |||||||||
| 2 | 131661 | YES | 61_Cart | NO | rtwrigh | NO | ght Tri | NO | xie 13. | NO | Tri Axi | NO | 952570_13 | NO | 2 | ||
| 3 | 131661 | YES | 61_Cart | NO | rtwrigh | NO | ght Tri | NO | xie 13. | NO | Tri Axi | NO | 952570_13 | NO | 2 | ||
| 4 | 131661 | YES | 61_Cart | NO | rtwrigh | NO | ght Tri | NO | xie 13. | NO | Tri Axi | NO | 952570_13 | NO | 2 | ||
| 5 | e : C2 | NO | C273048 | YES | 48 : 01 | NO | 01/01/2 | NO | to 31/0 | NO | 1/22 to | NO | CRD Hire | NO | 4 | ||
| 6 | e : C2 | NO | C273048 | YES | 48 : 01 | NO | 01/02/2 | NO | to 28/0 | NO | 2/22 to | NO | CRD Hire | NO | 4 | ||
| 7 | e : C2 | NO | C273048 | YES | 48 : 01 | NO | 01/03/2 | NO | to 18/0 | NO | 3/22 to | NO | CRD Hire | NO | 4 | ||
| 8 | in Fee | NO | ee : C2 | NO | C273048 | YES | 48 : 01 | NO | 1/22 to | NO | 01/01/ | NO | CRD Admin | NO | 6 | ||
| 9 | in Fee | NO | ee : C2 | NO | C273048 | YES | 48 : 01 | NO | 2/22 to | NO | 01/02/ | NO | CRD Admin | NO | 6 | ||
| 10 | in Fee | NO | ee : C2 | NO | C273048 | YES | 48 : 01 | NO | 3/22 to | NO | 01/03/ | NO | CRD Admin | NO | 6 | ||
| 11 | in Fee | NO | ee : C2 | NO | C286911 | YES | 11 : 01 | NO | 2/22 to | NO | 01/02/ | NO | CRD Admin | NO | 6 | ||
| 12 | in Fee | NO | ee Stub | NO | ub : C2 | NO | C273048 | YES | 20/12/ | NO | 048 : 2 | NO | CRD Admin | NO | 8 | ||
| 13 | in Fee | NO | ee Stub | NO | ub : C2 | NO | C273048 | YES | 20/12/ | NO | 048 : 2 | NO | CRD Admin | NO | 8 | ||
| 14 | oc : C | NO | C45575 | NO | 752 : 2 | NO | 26/02/ | NO | : RYDE | NO | 02/22 : | NO | CRD Adhoc | YES | 14 | ||
| 15 | oc : C | NO | C46214 | NO | 142 : 2 | NO | 26/02/ | NO | : RYDE | NO | 02/22 : | NO | CRD Adhoc | YES | 14 | ||
| 16 | L CRD | NO | D Admin | NO | in Fee | NO | e Stub | NO | C273048 | YES | ub : C2 | NO | REVERSAL | NO | 10 | ||
| 17 | L CRD | NO | D Admin | NO | in Fee | NO | e Stub | NO | C349902 | YES | ub : C3 | NO | REVERSAL | NO | 10 | ||
| 18 | L CRD | NO | D Hire | NO | e Stub | NO | b : C27 | NO | 48 : 20 | NO | C273048 | YES | REVERSAL | NO | 12 | ||
| 19 | L CRD | NO | D Hire | NO | e Stub | NO | b : C34 | NO | 02 : 20 | NO | C349902 | YES | REVERSAL | NO | 12 | ||
Sheet1 | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| O2:O19 | O2 | =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
