vlookup for multiple columns

[Guest]

Hi,

I have a set of data in one worksheet which contains 7 columns and multiple
rows of data (over 300 rows). In a new worksheet, I need to summarize one
column of this data which is referenced by matching 2 (or more columns).
Please see below:

Sheet 1 contains the data with 7 columns:
A B C D E F G H
110 03 2A 1777 03 3000 1555 456
111 04 5A 2587 02 3156 1777 12.65
etc etc etc etc for over 300 rows of data

Sheet 2 is where I wish to pull the data from Sheet 1----I would like to
return a value from Sheet 1 Column H based on criteria matching Column A,
column C and column F. In this example, I want to find all values in Column
H where Column F=3000, Column C=5A and Column A=110.

Any guidance or suggestions would be greatly appreciated. Thanks.

 

[Bob Phillips]

=INDEX(H1:H100,MATCH("3000"&"5A"&"110",F1:F100&C1:C100&A1:A100,0))

of course you can replace the values with cell references.

This is an array formula so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for your help! Do you know how I can copy this formula down some
rows? The drag function won't work since it is an array formula.

 

[Jason Morin]

You *can* drag array formulas. Before you do, change all
your range references to absolute references, press
ctrl/shift/enter, then fill down:

=INDEX($H$1:$H$100,MATCH
("3000"&"5A"&"110",$F$1:$F$100&$C$1:$C$100&$A$1:$A$100,0))

HTH
Jason
Atlanta, GA

 

[Peo Sjoblom]

The drag copy function has nothing to do if the formula is an array or not,
if you want to lookup values that are in cells
replace "3000", "5A" and "110" with their cell references and make all other
ranges absolute, could look like


=INDEX($H$1:$H$100,MATCH(H2&I2&J2,$F$1:$F$100&$C$1:$C$100&$A$1:$A$100,0))

where the different lookup values would be in H2, I2 and J2 and copied down
they will change to H3, I3 and J3

that is what Bob said as well

"of course you can replace the values with cell references"
--

Regards,

Peo Sjoblom

 

[Peo Sjoblom]

I doubt that it will change anything albeit be copied down<g>

 

[Guest]

Thanks for your help!

You *can* drag array formulas. Before you do, change all
your range references to absolute references, press
ctrl/shift/enter, then fill down:

=INDEX($H$1:$H$100,MATCH
("3000"&"5A"&"110",$F$1:$F$100&$C$1:$C$100&$A$1:$A$100,0))

HTH
Jason
Atlanta, GA