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.

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.

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