vlookup for multiple columns
 

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.

=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)


"MXC" wrote in message
...
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.

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.

"Bob Phillips" wrote:

=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)


"MXC" wrote in message
...
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.

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

-----Original Message-----
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.

"Bob Phillips" wrote:

=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)


"MXC" wrote in message
news:8477C7C1-F39B-4E46-8236-
...
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.



.

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



"MXC" wrote in message
...
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.

"Bob Phillips" wrote:

=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)


"MXC" wrote in message
...
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.

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

--

Regards,

Peo Sjoblom


"Jason Morin" wrote in message
...
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

-----Original Message-----
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.

"Bob Phillips" wrote:

=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)


"MXC" wrote in message
news:8477C7C1-F39B-4E46-8236-
...
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.



.

Thanks for your help!

"Jason Morin" wrote:

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

-----Original Message-----
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.

"Bob Phillips" wrote:

=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)


"MXC" wrote in message
news:8477C7C1-F39B-4E46-8236-
...
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.



.