+ Reply to Thread
Results 1 to 5 of 5

Find column and use it in a formula

  1. #1
    Registered User
    Join Date
    04-02-2020
    Location
    mexico city
    MS-Off Ver
    2016
    Posts
    3

    Find column and use it in a formula

    Hello!
    I have trouble to come up with a formula. I have a data base that is downloaded from a system, each time the order of the columns is different.

    I need to formulate, for example the sum of the number of "Tables", i.e. column B, so it would be =sum(b:b). This does not work for me because maybe next time the column with information about Tables is another different from B.

    Capture.JPG

    So I need a formula that identifies in which column is the information needed and that can be inside of another calculation formula. E.g. =sum(b:b) ,substitute b:b with a formula that identifies the column.

    I though of using =match("tables",2:2,0) ,it would show is column #2 and then insert it into the formula I need, but I don't know how.

    Any ideas would be very appreciatted!
    Last edited by pajg; 04-02-2020 at 07:27 PM.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

    Re: Find column and use it in a formula

    This formula may work for you. The first range is the header row, and the second range is the data range. Adjust your ranges as needed.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note: This sums the column of values for the string Tables, it doesn't act as a pointer to that column.

  3. #3
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    531

    Re: Find column and use it in a formula

    =SUMPRODUCT((B3:D5)*((B2:D2)="Tables"))

  4. #4
    Registered User
    Join Date
    04-02-2020
    Location
    mexico city
    MS-Off Ver
    2016
    Posts
    3

    Re: Find column and use it in a formula

    Thank you very much for your help!

    Now let me change the question. I need for example to bring to cell D7 the #erasers that Peter Wilson has.
    I am using formula: =INDEX(A1:E5,MATCH(1,(A:A=A7)*(E:E=B7),0),MATCH(C7,1:1,0)) Ctr + Shift
    What if every time I download the information the columns are in different order? is there a way to find in which column is the information of Name and substitute it for A:A?

    Capture.JPG
    Last edited by pajg; 04-02-2020 at 08:18 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    531

    Re: Find column and use it in a formula

    Quote Originally Posted by pajg View Post
    thank you very much for your help!

    Now let me change the question. I need for example to bring to cell d7 the #erasers that peter wilson has.
    I am using formula: =index(a1:e5,match(1,(a:a=a7)*(e:e=b7),0),match(c7,1:1,0)) ctr + shift
    what if every time i download the information the columns are in different order? Is there a way to find in which column is the information of name and substitute it for a:a?

    Attachment 670422
    =sumproduct((a2:a5=a6)*(e2:e5=b6)*(b1:d1=c6)*($b$2:$d$5))

+ 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. Excel formula to find text from column B in column A
    By chumpkin in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2019, 06:12 AM
  2. [SOLVED] Formula to find value in last cell in column with numerical data NOT a formula
    By GregM56 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-24-2019, 08:51 AM
  3. Replies: 6
    Last Post: 01-14-2019, 02:05 PM
  4. [SOLVED] Formula Index Column to find first blank cell and sum cells in different Column
    By Hood in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-18-2017, 07:57 PM
  5. i need to find a formula to find the top three values in a column
    By silentscribbles in forum Excel General
    Replies: 1
    Last Post: 11-07-2017, 04:07 AM
  6. Replies: 5
    Last Post: 03-18-2015, 10:14 AM
  7. Formula to find all matches in column A and return cells from column C
    By GenericPat in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2012, 04:56 PM

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