# Find and Match a Column in a Table to a Given Column and Insert If Formula

1. ## Find and Match a Column in a Table to a Given Column and Insert If Formula

Hi,

Can someone help me please? The sample data is attached. A need to insert a '1' in "Mobile Reviews?" and "Confirmed User?" columns if a value in 'Mobile Reviews" equals 'True' and a value in "Confirmed User?" equals 'Yes'..

I inserted IF formula in column'A' and column'B' (below) but the problem is that database has free entry text fields which change its order in the spreadsheet every time when the report in C1:H36 is pulled. For example, "Mobile Reviews" column which is column 'F' in the attached file can be in column 'H' or any other column. So I need to insert IF formula in the column 'A' and column'B' (below) but need to search in the table C1:H36 for a correct location of these two columns. In addition, the total number of columns changes every time I pull a report, it can be 10 or 20. I think I should use match and index formula but was not able to figure it out. Sample Data.xlsx
h
column'A': =IF(F2="","",IF(F2="True",1))
column'B': =IF(H2="","",IF(H2="Yes",1))

Can you please provide me with a formula which finds a column location and inserts IF formula?

Thanks so much!  Register To Reply

2. ## Re: Find and Match a Column in a Table to a Given Column and Insert If Formula

Trythis.

1. Remove the ? from the headings in A and B (They could stay, but then we would need to modify the formula)
Then...
A2=INDEX(C2:H2,MATCH(\$A\$1,\$C\$1:\$H\$1,0))
B2=INDEX(C2:H2,MATCH(\$B\$1,\$C\$1:\$H\$1,0))  Register To Reply

3. ## Re: Find and Match a Column in a Table to a Given Column and Insert If Formula

Hi FDibbins,

The problem is that I need '1' values in columns 'A' and 'B' because I create a pivot table with calculated values based on these two columns. In addition, the number of columns can vary and usually goes beyond column 'H'. The formula that you provided doesn't account for potentially expanded number of columns and doesn't return '1' because IF formula need to be embedded. Also, the two columns with the question marks should not be renamed because pivot table doesn't allow for duplicate column names.....

Your help is very much appreciated!!!  Register To Reply

4. ## Re: Find and Match a Column in a Table to a Given Column and Insert If Formula

Thats why I said to add what you needed to get the answers you wanted.

OK try this then. Adjust the formulas to...
A2=IF(INDEX(\$C2:\$H2,MATCH(LEFT(\$A\$1,LEN(\$A\$1)-1),\$C\$1:\$H\$1,0))=0,"",1)
B2=IF(INDEX(\$C2:\$H2,MATCH(LEFT(\$B\$1,LEN(\$A\$1)-1),\$C\$1:\$H\$1,0))=0,"",1)

In addition, the number of columns can vary and usually goes beyond column 'H'.
So then adjust the referenced to go out as far as you need them to. Either type in the new reference, or use F2 and then just drag it out further.

(you need to do some of the work here lol)  Register To Reply

5. ## Re: Find and Match a Column in a Table to a Given Column and Insert If Formula

You are the BEST! Yes, I need to do some work here and will learn more about these formulas. I just needed it ASAP. Thank you!!! I added reputation to your profile.   Register To Reply

6. ## Re: Find and Match a Column in a Table to a Given Column and Insert If Formula

Happy to help, thanks for the kind words and the feedback   Register To Reply