match in multi-column and multi-row array

S

sloth

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?
 
R

RagDyer

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
 
R

RagDyer

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!
 
P

Pete_UK

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
 
P

Pete_UK

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
 
R

RagDyer

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!
 
R

RagDyer

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

Pete_UK

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
 
P

Pete_UK

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
 
R

RagDyer

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

Pete_UK

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
 
S

sloth

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