Thread: Vlookup, match, or sumproduct?

Hello,

I've got two ranges on a sheet, and I need to match a row/column
definition in Range 1 with the row/column definition in Range 2, and
then return a corresponding value in Range 2 to Range 1. Here's an
example of what I need:

Range 1

ColA ColB ColC ColD ColE
Row1 Control 3 30 100
Row2 a
Row3 b
Row4 c


Range 2

ColA ColB ColC
a 30 4.5
b 3 18
c Control 99


What I need to do is fill the cells in Range 1. For example,
matching D1&A2 (30a) in Range 1 with B1&A1 (30a) in Range 2, and
returning to D2 in Range 1 the corresponding value in Range 2 (4.5).
The values in Row1 of Range 1 and ColB of Range 2 will change.

I hope I've made that clear enough. I appreciate any advice. Please
let me know if you need more or amplifying info to help. Thanks!

Frank

One way ..

Assuming range2 is within A1:C3 in Sheet2,
range1 is within cols A to E in Sheet1
(with col headers in B1:E1, row headers in A2 down)

In Sheet1,

Put in B2's formula bar, then array-enter by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=INDEX(Sheet2!$C$1:$C$3,MATCH(1,(Sheet2!$A$1:$A$3=$A2)*(Sheet2!$B$1:$B$3=B$1),0))
Copy across and fill down to populate the grid

Adapt the ranges to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Phrank" wrote:
> Hello,
>
> I've got two ranges on a sheet, and I need to match a row/column
> definition in Range 1 with the row/column definition in Range 2, and
> then return a corresponding value in Range 2 to Range 1. Here's an
> example of what I need:
>
> Range 1
>
> ColA ColB ColC ColD ColE
> Row1 Control 3 30 100
> Row2 a
> Row3 b
> Row4 c
>
>
> Range 2
>
> ColA ColB ColC
> a 30 4.5
> b 3 18
> c Control 99
>
>
> What I need to do is fill the cells in Range 1. For example,
> matching D1&A2 (30a) in Range 1 with B1&A1 (30a) in Range 2, and
> returning to D2 in Range 1 the corresponding value in Range 2 (4.5).
> The values in Row1 of Range 1 and ColB of Range 2 will change.
>
> I hope I've made that clear enough. I appreciate any advice. Please
> let me know if you need more or amplifying info to help. Thanks!
>
> Frank
>