Solved

# Excel Lookup Question

Posted on 2011-10-05
185 Views
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
Question by:trbbhm

LVL 29

Expert Comment

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

LVL 24

Expert Comment

Is it always a number four rows below the company name?
0

LVL 24

Expert Comment

Maybe post a workbook.
0

Author Comment

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

LVL 24

Accepted Solution

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

LVL 26

Assisted Solution

... or
=OFFSET(C\$1,MATCH(\$I2,\$A\$2:\$A\$25,0)+5,0)

Regards,
Brian.
0

Author Closing Comment

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

LVL 26

Expert Comment

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

## Featured Post

### Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.