• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Excel Lookup Question

I have a spreadsheet where I need to lookup a total based on a value.  My data looks like this

ABC CO     1-2           3-4          5-6
      JAN       500          600        550
      FEB       450          750        575
      MAR      425          700        600
                  1375        2050      1725
BIGCO     1-2           3-4          5-6
      JAN       500          600        550
      FEB       450          750        575
      MAR      425          700        600
                  1375        2050      1725

In a spreadsheet I want to be able to lookup the total columns for a given company.  For example:

BIGCO    1375
ABC CO  1375

How do I phrase the lookup for what I want to do?  Is this possible?
0
trbbhm
Asked:
trbbhm
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Randy DownsOWNERCommented:
maybe this will help

http://www.contextures.com/xlfunctions02.html

Excel VLOOKUP Function Arguments
The Excel VLOOKUP function has four arguments:

lookup_value: What value do you want to look up? In this example, the product code is in cell A7, and you want to find its product name.
table_array: Where is the lookup table? If you use an absolute reference ($A$2:$C$5), instead of a relative reference (A2:C5), it will be easier to copy to formula to other cells. Or, name the lookup table, and refer to it by name.
col_index_num: Which column has the value you want returned? In this example, the product names are in the second column of the lookup table.
[range_lookup]: Do you want an exact match? Is an approximate match okay?
If you use TRUE as the last argument, or omit the last argument, an approximate match can be returned. This example has FALSE as the last argument, so if the product code is not found, the result will be #N/A. (Note: Excel is rather forgiving, and will accept 0 instead of FALSE, and 1 instead of TRUE
0
 
StephenJRCommented:
Is it always a number four rows below the company name?
0
 
StephenJRCommented:
Maybe post a workbook.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
trbbhmAuthor Commented:
StephenJR:  The example I listed was basically a condensed version, but you get the idea.  The number I want to lookup is *always* 12 rows below the company name.

I've attached an example spreadsheet to this file (again, it's a condensed version of the real thing, but it gets the point across.) example.xlsx
0
 
StephenJRCommented:
You could use this in J2 and copy across and down:

=INDEX($A$2:$E$25,MATCH($I2,$A$2:$A$25,0)+5,MATCH(J$1,$A$1:$E$1,0))

and change the +5 accordingly.
0
 
redmondbCommented:
... or
=OFFSET(C$1,MATCH($I2,$A$2:$A$25,0)+5,0)

Regards,
Brian.
0
 
trbbhmAuthor Commented:
Thank you StephenJR and redmondb for these answers!  Now if you could only replace the hair in my head that I've pulled out trying to figure this out!

Thanks guys!
0
 
redmondbCommented:
Thanks, trbbhm. (Just jealous you've still got hair to pull!)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now