Two Way Lookup using sumproduct

[arvarr]

Hi
Was wondering if someone can help out with formula.

What I'm trying to achieve is a two way lookup.
Example: I need to find out the value when the criteria is North in Column A and West in Row 1.

The formula below gives me the total.
It's great if all records are unique but is there a way of finding out the separate values. The separate values are 2 and 7. The sumproduct formula gives me 9.


A B C D
Mail West West
North 1 2 7
East 2 4 5
South 3 6 3
West 4 8 1

West
North =SUMPRODUCT((A2:A5=A8)*(B11=B7)*(B25))

Thanks.
Kind Regards,
arvarr

 

[SkipVought]

Hi,

That's because SUMPRODUCT is not a lookup function. It is an aggregation function.

Your example and formula are not consistent, and consequently jiberish!!! Please post clear and consistent information.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
So, is there any way that I can look use a two way lookup?
Thanks.

 

[arvarr]

Hi Skip
What's the ideal formula for a two way lookup?
Yes, the above is just an example and it clearly shows me result to expect.


Mail West West
North 1 2 7
East 2 4 5
South 3 6 3
West 4 8 1

Thanks.

 

[SkipVought]

Please explain EXACTLY what you want. I need lookup values consisten with the example data and the expected results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Data as follows:

Mail West West
North 1 2 7
East 2 4 5
South 3 6 3
West 4 8 1

Lookup Value = North (Column A)
Lookup Value = West (Row 1)

The answer would be 2 and 7.

Thanks.

 

[SkipVought]

The OFFSET function.

This function has 5 arguments and it returns a range.

Arg 1 would be A1

Arg 2 & 3 are the row & column offsets respectively. The row offset would be a MATCH lookup on column A. The column offset would be a MATCH lookup on row 1.

Args 4 7 5 are the row & column range counts respectively. The row count is the count of NORTH in column a using COUNTIF and the column count is the count of WEST in row 1 using COUNTIF.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Would you be able to give an example of how the offset formula will return the answer 2 and 7?
Thanks

 

[SkipVought]

It returns a RANGE.

Then you use that range in the INDEX function to return a value within the range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Assuming that G1 contains North and H1 contains West and F2 & F3 contain 1 & 2...
[tt]
G2: =INDEX(OFFSET($A$1,MATCH($G$1,$A$2:$A$5,0),MATCH($H$1,$B$1:$D$1,0),COUNTIF($A$2:$A$5,$G$1),COUNTIF($B$1:$D$1,$H$1)),F2)
[/tt]
and copy to G3

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Thanks Skip, yes it works.