Bring back value in last row based on column header In one data table, I have co

Question

Bring back value in last row based on column header

In one data table, I have colomn headers that are company names with values in, say, the next 300 rows, and a total in the last row. In another sheet, I would like to create a report by company name (in column A), with the totals in column B that would be obtained through a formula based on that company's name. For example, let's say my column header range is named CoName and my last row is named CoTotal. In the report sheet, I was hoping for a formula that would bring back the value in CoTotal based on the position in CoName of each company. I could use HLOOKUP, with MATCH for the company's position, but would have to specify the number of rows where the total is found, which I would like to avoid.

Thank you

Explanation / Answer

sAssuming your company names are in row 1 of Sheet 1,.

- In Sheet 2, put following formula in A1 and B1

A1: =OFFSET(Sheet1!$A$1,0,ROWS($1:1)-1,1,1)

B1: =INDEX(Sheet1!$A:$G,MATCH(10^99,OFFSET(Sheet1!A:A,0,1,)),MATCH(A1,Sheet1!$1:$1,0))

Sheet1$A:$G is for company names in Sheet1...Change $G as per your need.

O)rt try this

1. Suppose your Company Names are in A1:Z1 of sheet1 and data is in range A2:Z300

2. The total row appears in A301:Z301

3. On sheet2, Company Names appear in cell B2 downwards

4. In cell C2, enter this formula and copy down

=HLOOKUP(B2,$A$1:$Z$301,301,0)

^$**7

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.