+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    92

    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!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,744

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

    Add whatever you need to get the answers you want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    92

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

    Can you please account for all these conditions in your formula?

    Your help is very much appreciated!!!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,744

    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)

  5. #5
    Registered User
    Join Date
    11-21-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    92

    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.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,744

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find most recent date in Column Headings in a Range, insert new column and heading
    By mikey3580 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-07-2014, 08:28 AM
  2. Find 1st unique Value on Column and Insert certain Word on cell on left Column
    By Wldlife23 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2013, 10:02 AM
  3. [SOLVED] Formula to find header and insert in new column
    By dbracken1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2012, 03:50 AM
  4. Search in every column for a value in a specific row, then insert a column before if match
    By manueslapera in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-18-2012, 04:48 PM
  5. Find match column of data within CSE formula
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2012, 09:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1