Vlookup, match, or sumproduct?

[Phrank]

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

 

[Guest]

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