I have a dataset that consists of 4 full columns of data. On another
sheet I have a list of values (1000 values in column A.) I would like
to search in the dataset for an exact match with each value in my list
returning TRUE if there's a match and FALSE if there's no match (1
return value in column B for each original list value in column A.)

Is there a way to do this without using 4 vlookups? In addition, to
simplifying the formula, is there a way to do this using a formula that
doesn't take as long to calculate?

With your dataset in Sheet2, from A1 to D100,
And your list on another sheet, from A1 down,
Try this in B1, and copy down as needed:

=SUMPRODUCT(--(Sheet2!$A$1:$D$110=A1))<0

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
I have a dataset that consists of 4 full columns of data. On another
sheet I have a list of values (1000 values in column A.) I would like
to search in the dataset for an exact match with each value in my list
returning TRUE if there's a match and FALSE if there's no match (1
return value in column B for each original list value in column A.)

Is there a way to do this without using 4 vlookups? In addition, to
simplifying the formula, is there a way to do this using a formula that
doesn't take as long to calculate?

I don't know why I said dataset in A1 to D100,
And then have the formula reference A1 to D110,

But I'm sure you can figure it out!<g

Old age ... again!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RagDyer" wrote in message
...
With your dataset in Sheet2, from A1 to D100,
And your list on another sheet, from A1 down,
Try this in B1, and copy down as needed:

=SUMPRODUCT(--(Sheet2!$A$1:$D$110=A1))<0

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
I have a dataset that consists of 4 full columns of data. On another
sheet I have a list of values (1000 values in column A.) I would like
to search in the dataset for an exact match with each value in my list
returning TRUE if there's a match and FALSE if there's no match (1
return value in column B for each original list value in column A.)

Is there a way to do this without using 4 vlookups? In addition, to
simplifying the formula, is there a way to do this using a formula that
doesn't take as long to calculate?

Do you mean by "4 full columns of data" that you have data from row 1 to row
65536 in columns A to D? (If so, RD's SP formula couldn't be used). Is the
data in some kind of sequence, or randomly distributed? Can it be sorted (so
a binary search could be used)?

I have read somewhere that MATCH is quicker to calculate than VLOOKUP, but
I've not tested this out.

Pete

The data is in A1:D65536. The data is sorted ascending.

And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

If it is sorted within each column and sequentially from one column to
the next, so that the value in B1 is larger than the value in A65536,
then a quick check of the range A1:D1 will determine which of the
columns to look down, so by making use of the INDIRECT function you
would only need one VLOOKUP down the column with a MATCH to find which
column.

Does this help?

Pete

sloth wrote:
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

If you could eliminate a *single* row, the Sumproduct formula would do the
trick:

=SUMPRODUCT(--(Sheet2!$A$2:$D$65536=A1))<0

This calculates in *less* then 4 seconds!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

Indirect is volatile ... and with that huge range ... I don't think that's
wise!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pete_UK" wrote in message
oups.com...
If it is sorted within each column and sequentially from one column to
the next, so that the value in B1 is larger than the value in A65536,
then a quick check of the range A1:D1 will determine which of the
columns to look down, so by making use of the INDIRECT function you
would only need one VLOOKUP down the column with a MATCH to find which
column.

Does this help?

Pete

sloth wrote:
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

I put 1 in A1 and then incremented this by 3 down columns A to D to
simulate what I think the OP has (or should have).

F1 is for the number to be found, with this formula in G1:

=MATCH(F1,A1:D1)

and this formula in H1:

=MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1+64)),0)

This returns #N/A if the number in F1 is not found (easily trapped with
IF(ISNA( ... etc) and the row number that the number is on if the
sought-number is present. The response is instant (well I can't detect
any delay after entering a new number in F1).

I think the OP wanted a True or False result, so change the formula in
H1 to:

=IF(ISNA(MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1 +64)),0)),FALSE,TRUE)

and hide column G.

Hope this helps.

Pete

RagDyer wrote:
If you could eliminate a *single* row, the Sumproduct formula would do the
trick:

=SUMPRODUCT(--(Sheet2!$A$2:$D$65536=A1))<0

This calculates in *less* then 4 seconds!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

I've just re-read what the OP has - 1000 values in another sheet, so
assume the data is on Sheet1 and the 1000 values in A1 to A1000, with
these formula in B1 and C1:

B1: =MATCH(A1,Sheet1!A$1:D$1)
C1:
=IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&CHAR(B1+64)&" :"&CHAR(B1+64)),0)),FALSE,TRUE)

Then copy these down to row 1000 - it takes about 4 - 5 seconds to
recalculate.

Hope this helps - he's probably gone to bed by now !! <bg

Pete

Pete_UK wrote:
I put 1 in A1 and then incremented this by 3 down columns A to D to
simulate what I think the OP has (or should have).

F1 is for the number to be found, with this formula in G1:

=MATCH(F1,A1:D1)

and this formula in H1:

=MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1+64)),0)

This returns #N/A if the number in F1 is not found (easily trapped with
IF(ISNA( ... etc) and the row number that the number is on if the
sought-number is present. The response is instant (well I can't detect
any delay after entering a new number in F1).

I think the OP wanted a True or False result, so change the formula in
H1 to:

=IF(ISNA(MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1 +64)),0)),FALSE,TRUE)

and hide column G.

Hope this helps.

Pete

RagDyer wrote:
If you could eliminate a *single* row, the Sumproduct formula would do the
trick:

=SUMPRODUCT(--(Sheet2!$A$2:$D$65536=A1))<0

This calculates in *less* then 4 seconds!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

In duplicating the exact OP conditions, both formulas take approximately the
same time to calculate IF a single value is changed in the 1000 row list ...
BUT ... to build the formulas in the adjoining column ... there was a
gigantic time difference.

Your 2 columns of formulas filled down in approx. 3 or 4 seconds.
The single Sumproduct column took in excess of *5 minutes*.

When changing a block of values (25) in the 1000 row list, your formulas
took maybe 2 seconds, while the Sumproduct took about 15 seconds.

I would say that your suggested formulas are definitely the better way to
go.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pete_UK" wrote in message
oups.com...
I've just re-read what the OP has - 1000 values in another sheet, so
assume the data is on Sheet1 and the 1000 values in A1 to A1000, with
these formula in B1 and C1:

B1: =MATCH(A1,Sheet1!A$1:D$1)
C1:
=IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&CHAR(B1+64)&" :"&CHAR(B1+64)),0)),FALSE,TRUE)

Then copy these down to row 1000 - it takes about 4 - 5 seconds to
recalculate.

Hope this helps - he's probably gone to bed by now !! <bg

Pete

Pete_UK wrote:
I put 1 in A1 and then incremented this by 3 down columns A to D to
simulate what I think the OP has (or should have).

F1 is for the number to be found, with this formula in G1:

=MATCH(F1,A1:D1)

and this formula in H1:

=MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1+64)),0)

This returns #N/A if the number in F1 is not found (easily trapped with
IF(ISNA( ... etc) and the row number that the number is on if the
sought-number is present. The response is instant (well I can't detect
any delay after entering a new number in F1).

I think the OP wanted a True or False result, so change the formula in
H1 to:

=IF(ISNA(MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1 +64)),0)),FALSE,TRUE)

and hide column G.

Hope this helps.

Pete

RagDyer wrote:
If you could eliminate a *single* row, the Sumproduct formula would do
the
trick:

=SUMPRODUCT(--(Sheet2!$A$2:$D$65536=A1))<0

This calculates in *less* then 4 seconds!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

Thanks, RD - so what I'd read about MATCH being quick (I think it was
on the Decision Models site) seems to be true !!

I think this slight variation in the formulae should be quicker still:

B1: =CHAR(MATCH(A1,Sheet1!A$1:D$1)+64)
C1: =IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&B1&":"&B1),0) ),FALSE*,TRUE)

Hopefully the OP will be able to try it out and report back.

Hope this helps.

Pete

RagDyer wrote:
In duplicating the exact OP conditions, both formulas take approximately the
same time to calculate IF a single value is changed in the 1000 row list ....
BUT ... to build the formulas in the adjoining column ... there was a
gigantic time difference.

Your 2 columns of formulas filled down in approx. 3 or 4 seconds.
The single Sumproduct column took in excess of *5 minutes*.

When changing a block of values (25) in the 1000 row list, your formulas
took maybe 2 seconds, while the Sumproduct took about 15 seconds.

I would say that your suggested formulas are definitely the better way to
go.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pete_UK" wrote in message
oups.com...
I've just re-read what the OP has - 1000 values in another sheet, so
assume the data is on Sheet1 and the 1000 values in A1 to A1000, with
these formula in B1 and C1:

B1: =MATCH(A1,Sheet1!A$1:D$1)
C1:
=IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&CHAR(B1+64)&" :"&CHAR(B1+64)),0)),FALSE,TRUE)

Then copy these down to row 1000 - it takes about 4 - 5 seconds to
recalculate.

Hope this helps - he's probably gone to bed by now !! <bg

Pete

Pete_UK wrote:
I put 1 in A1 and then incremented this by 3 down columns A to D to
simulate what I think the OP has (or should have).

F1 is for the number to be found, with this formula in G1:

=MATCH(F1,A1:D1)

and this formula in H1:

=MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1+64)),0)

This returns #N/A if the number in F1 is not found (easily trapped with
IF(ISNA( ... etc) and the row number that the number is on if the
sought-number is present. The response is instant (well I can't detect
any delay after entering a new number in F1).

I think the OP wanted a True or False result, so change the formula in
H1 to:

=IF(ISNA(MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1 +64)),0)),FALSE,TRUE)

and hide column G.

Hope this helps.

Pete

RagDyer wrote:
If you could eliminate a *single* row, the Sumproduct formula would do
the
trick:

=SUMPRODUCT(--(Sheet2!$A$2:$D$65536=A1))<0

This calculates in *less* then 4 seconds!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

Thanks to both of you for the guidance. I ended up using the method
suggested by Pete. It was exactly what I was looking for. Lots of
good learnings packed in that solution. No issue with speed.




Pete_UK wrote:
Thanks, RD - so what I'd read about MATCH being quick (I think it was
on the Decision Models site) seems to be true !!

I think this slight variation in the formulae should be quicker still:

B1: =CHAR(MATCH(A1,Sheet1!A$1:D$1)+64)
C1: =IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&B1&":"&B1),0) ),FALSE*,TRUE)

Hopefully the OP will be able to try it out and report back.

Hope this helps.

Pete

RagDyer wrote:
In duplicating the exact OP conditions, both formulas take approximately the
same time to calculate IF a single value is changed in the 1000 row list ...
BUT ... to build the formulas in the adjoining column ... there was a
gigantic time difference.

Your 2 columns of formulas filled down in approx. 3 or 4 seconds.
The single Sumproduct column took in excess of *5 minutes*.

When changing a block of values (25) in the 1000 row list, your formulas
took maybe 2 seconds, while the Sumproduct took about 15 seconds.

I would say that your suggested formulas are definitely the better way to
go.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pete_UK" wrote in message
oups.com...
I've just re-read what the OP has - 1000 values in another sheet, so
assume the data is on Sheet1 and the 1000 values in A1 to A1000, with
these formula in B1 and C1:

B1: =MATCH(A1,Sheet1!A$1:D$1)
C1:
=IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&CHAR(B1+64)&" :"&CHAR(B1+64)),0)),FALSE,TRUE)

Then copy these down to row 1000 - it takes about 4 - 5 seconds to
recalculate.

Hope this helps - he's probably gone to bed by now !! <bg

Pete

Pete_UK wrote:
I put 1 in A1 and then incremented this by 3 down columns A to D to
simulate what I think the OP has (or should have).

F1 is for the number to be found, with this formula in G1:

=MATCH(F1,A1:D1)

and this formula in H1:

=MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1+64)),0)

This returns #N/A if the number in F1 is not found (easily trapped with
IF(ISNA( ... etc) and the row number that the number is on if the
sought-number is present. The response is instant (well I can't detect
any delay after entering a new number in F1).

I think the OP wanted a True or False result, so change the formula in
H1 to:

=IF(ISNA(MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1 +64)),0)),FALSE,TRUE)

and hide column G.

Hope this helps.

Pete

RagDyer wrote:
If you could eliminate a *single* row, the Sumproduct formula would do
the
trick:

=SUMPRODUCT(--(Sheet2!$A$2:$D$65536=A1))<0

This calculates in *less* then 4 seconds!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.

Oh well, I'm glad it worked for you - thanks for the feedback.

Pete

sloth wrote:
Thanks to both of you for the guidance. I ended up using the method
suggested by Pete. It was exactly what I was looking for. Lots of
good learnings packed in that solution. No issue with speed.




Pete_UK wrote:
Thanks, RD - so what I'd read about MATCH being quick (I think it was
on the Decision Models site) seems to be true !!

I think this slight variation in the formulae should be quicker still:

B1: =CHAR(MATCH(A1,Sheet1!A$1:D$1)+64)
C1: =IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&B1&":"&B1),0) ),FALSE*,TRUE)

Hopefully the OP will be able to try it out and report back.

Hope this helps.

Pete

RagDyer wrote:
In duplicating the exact OP conditions, both formulas take approximately the
same time to calculate IF a single value is changed in the 1000 row list ...
BUT ... to build the formulas in the adjoining column ... there was a
gigantic time difference.

Your 2 columns of formulas filled down in approx. 3 or 4 seconds.
The single Sumproduct column took in excess of *5 minutes*.

When changing a block of values (25) in the 1000 row list, your formulas
took maybe 2 seconds, while the Sumproduct took about 15 seconds.

I would say that your suggested formulas are definitely the better way to
go.<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pete_UK" wrote in message
oups.com...
I've just re-read what the OP has - 1000 values in another sheet, so
assume the data is on Sheet1 and the 1000 values in A1 to A1000, with
these formula in B1 and C1:

B1: =MATCH(A1,Sheet1!A$1:D$1)
C1:
=IF(ISNA(MATCH(A1,INDIRECT("Sheet1!"&CHAR(B1+64)&" :"&CHAR(B1+64)),0)),FALSE,TRUE)

Then copy these down to row 1000 - it takes about 4 - 5 seconds to
recalculate.

Hope this helps - he's probably gone to bed by now !! <bg

Pete

Pete_UK wrote:
I put 1 in A1 and then incremented this by 3 down columns A to D to
simulate what I think the OP has (or should have).

F1 is for the number to be found, with this formula in G1:

=MATCH(F1,A1:D1)

and this formula in H1:

=MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1+64)),0)

This returns #N/A if the number in F1 is not found (easily trapped with
IF(ISNA( ... etc) and the row number that the number is on if the
sought-number is present. The response is instant (well I can't detect
any delay after entering a new number in F1).

I think the OP wanted a True or False result, so change the formula in
H1 to:

=IF(ISNA(MATCH(F1,INDIRECT(CHAR(G1+64)&":"&CHAR(G1 +64)),0)),FALSE,TRUE)

and hide column G.

Hope this helps.

Pete

RagDyer wrote:
If you could eliminate a *single* row, the Sumproduct formula would do
the
trick:

=SUMPRODUCT(--(Sheet2!$A$2:$D$65536=A1))<0

This calculates in *less* then 4 seconds!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"sloth" wrote in message
oups.com...
And yes it can be sorted.

sloth wrote:
The data is in A1:D65536. The data is sorted ascending.