# 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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### 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
2
Views
162
Replies
0
Views
371
Replies
5
Views
239
Replies
9
Views
226
Replies
20
Views
1K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,837
Messages
5,766,721
Members
425,373
Latest member
ndiejennrrd

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