# Formula Needed for: FIND "YES" and Return in same cell as YES

#### NTCICmac

##### New Member
Hi MrExcel Community,

See below for a sample of data that I need to extract information into a searchable database. All of the information is provided in both columns and rows. I'd like to create TABLE 1 into TABLE 2. What's most frustrating is identifying project type by finding the YES and figuring out how to return the project type which is in the same cell!

What would be the most efficient formula to convert TABLE 1 to TABLE 2 format?

Thank you!

TABLE 1
 Project # Project Owner Commercial: Bank: Project Name Owner Address School: Carriage House: Project Address Owner City, State Zip Office: Industrial: Project City, State Zip Theatre: Rail Station: Project Date Single Family Housing: Jail: Project Cost Multi-family Housing (2): Private Club: Multi-family Housing (3+): YES Public: Hotel/B&B: Church: Hospital: Barn: Other:

<tbody>
</tbody>

TABLE 2
 Project # Project Name Project Address Project City, ST Zip Project Date Project Cost Project Owner Owner Address Owner City, ST Zip Project Type Project # Project Name Project Address Project City, ST Zip Project Date Project Cost Project Owner Owner Address Owner City, ST Zip Multi-family Housing (3+)

<tbody>
</tbody>

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### ExcelSpeedster

##### Board Regular
If you have the value "Multi-family Housing (3+): YES" in cell A1, the following formula will check for a "YES" and extract the Project Type only:
=IF(SEARCH("YES",A1)>-1,LEFT(A1,SEARCH(":",A1)-1))

If you want to check for a "YES" in any Project Type, you're better of using a macro to accomplish this.

#### NTCICmac

##### New Member
If you have the value "Multi-family Housing (3+): YES" in cell A1, the following formula will check for a "YES" and extract the Project Type only:
=IF(SEARCH("YES",A1)>-1,LEFT(A1,SEARCH(":",A1)-1))

If you want to check for a "YES" in any Project Type, you're better of using a macro to accomplish this.

I do want to check for a YES for any project type. Any tips on how to create a macro for it? Thanks!

#### ExcelSpeedster

##### Board Regular
Any tips on how to create a macro for it? Thanks!
Please send me an example file. I'll need to understand a bit more about your workflow here.

Are you updating multiple lines in Table 2 at the same time, or do you only update Table 2 with a single line once Table 1 has been completed by a user?

#### NTCICmac

##### New Member
Please send me an example file. I'll need to understand a bit more about your workflow here.

Are you updating multiple lines in Table 2 at the same time, or do you only update Table 2 with a single line once Table 1 has been completed by a user?

Thanks so much for responding! A colleague was able to figure it out for me with 3 nested formulas:

=IF(RIGHT(OFFSET(\$A5,R\$2,R\$3),3)="yes",R\$1,"") for Commercial
=IF(RIGHT(OFFSET(\$A5,S\$2,S\$3),3)="yes",S\$1,"") for School
=IF(RIGHT(OFFSET(\$A5,T\$2,T\$3),3)="yes",T\$1,"") for Office
etc.

Problem solved! Thank you all!

Replies
0
Views
263
Replies
2
Views
339
Replies
0
Views
747
Replies
5
Views
473
Replies
9
Views
559

1,186,165
Messages
5,956,332
Members
438,247
Latest member
UZev

### 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.

### Which adblocker are you using?

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

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