Using Vlookup with Sumproduct to satisfy multiple conditions

[shawnteojc]

Hi everyone,

I have been pondering this for many days without being able to obtain
a solution. With the following sample data,

Table 1
City Country
Sydney Australia
Madrid Spain
Lisbon Portugal
Brisbane Australia
Tokyo Japan
Geneva Switzerland
Zurich Switzerland



Table 2
# of Visits Favorite Birthplace
2 Sydney Switzerland
1 Zurich Switzerland
4 Tokyo Australia
2 Brisbane Spain
3 Tokyo Japan
2 Lisbon Japan
3 Madrid Switzerland
1 Madrid Japan
3 Zurich Australia


I would like to calculate the number of entries where the number of
visits is either 2 or 3 AND that the Favorite City is not located in
the same Birthplace, ie. (3 Tokyo Japan) should not be considered but
(3 Madrid Switzerland) should?

Is it possible to use a combination of vlookup and sumproduct to
obtain the answer? If possible I would prefer not to add new columns
to Table 2.

Really appreciate any help. Thanks in advance.


Shawn

 

[Max]

Until something better drifts by ..

Data for table 1 asssumed in A3:B9,
that for table 2 assumed in A14:C22

In D14, copied down to D22:
=--(INDEX($B$3:$B$9,MATCH(B14,$A$3:$A$9,0))=C14)

D1422 provides the references to handle that criteria bit on the city <>
birthplace

Then you could use in say, A25:
=SUMPRODUCT((A14:A22={2,3})*(D1422=0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik

 

[shawnteojc]

Max,

Thank you.

the formula works. A few questions though:

1) is it possible to do the job without adding a new column to table
2?

2) Could you enlighten me on the use of "--" preceding your formula?

Thanks.


Cheers,
Shawn

 

[Max]

Shawn

the formula works.

That's good, because that's usually the most important thing at hand

1) is it possible to do the job without adding a new column to table2?

I couldn't find a way to do this. That's why my topliner:But maybe other responders do. Hang around awhile.

2) Could you enlighten me on the use of "--" preceding your formula?

The "--" will coerce TRUEs/FALSEs returned by the comparison formula:
INDEX($B$3:$B$9,MATCH(B14,$A$3:$A$9,0))=C14
to numeric 1's/0's
(Sumproduct works with numbers)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,700, Files: 356, Subscribers: 53
xdemechanik
---

 

[shawnteojc]

I have tried using the LOOKUP function for this as well but I think
there is a problem, meaning the LOOKUP function doesn't seem to return
the correct values. I may not have understood this correctly.

This is the formula i am using:

Data for table 1 asssumed in A3:B9,
that for table 2 assumed in A14:C22

=SUMPRODUCT((A14:A22={2,3})*(LOOKUP(B14:B22,A3:B9)<>C14:C22))

Perhaps i am not using the LOOKUP correctly.

Would really appreciate any help.

Thanks.


Shawn

 

[shawnteojc]

I have triedusingthe LOOKUP function for this as well but I think
there is a problem, meaning the LOOKUP function doesn't seem to return
the correct values. I may not have understood this correctly.

Hi I am still stuck on this.

Is there no one who could help me with this?

Thanks in advance.


This is the formula i amusing:

 

[T. Valko]

I *think* this does what you want. Based on your posted sample data I get a
result of 5.

Table 1 in the range A2:B8
Table 2 in the range D2:F10

=SUMPRODUCT((D210={2,3})*(ISNA(MATCH(E2:E10&F2:F10,A2:A8&B2:B8,0))))

 

[Max]

Nicely done, Biff. Inventive.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000, Files:358, Subscribers:55
xdemechanik

 

[T. Valko]

Thanks, Max!

Just hope my interpretation is right!

--
Biff
Microsoft Excel MVP

Nicely done, Biff. Inventive.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000, Files:358, Subscribers:55
xdemechanik
---

 

[Max]

Just hope my interpretation is right!

I believe so
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---