The formula is correct and I've double checked that it works for text or
numeric arguments.

You need to go over your 'matrix', somewhere in there is an error producing
the excel #NUM! error message.

Quickest way is to select the entire sheet, CTRL+F to open the Find, then
look for #NUM! being sure to select 'Look in Values' in the options.

Giz

"GaryG" wrote:

Cheers for your help but this isn't working for me. This is the forumla i
entered

=SUMPRODUCT(--(pallets!$1:$65536=moves!$A$1))

Sheet 2 being "pallets" which is the matrix

Sheet 1 being "moves" which holds the data i want to check exists in "pallets"

Error is #NUM

"Gizmo63" wrote:

OK, you have a value in sheet1!A1 that you need to verify exists in the data
held in sheet2!$A$1:$IV$65536.

This formula in say sheet1!A2 will return a TRUE or FALSE (1 or 0) answer
depending if the test value is in the 'matrix'.

=SUMPRODUCT(--(sheet2!$A$1:$IV$65536=sheet1!$A$1))

HTH

Giz

"GaryG" wrote:

Hi, thats correct i have a lot of data scattered across columns & rows, i
want to use a vlookup on another sheet to return these values.

"Gizmo63" wrote:

Judging by your posts what I guess you're trying to achieve is a check to see
if a particular value exists in your matrix.

This is the only reason I can see for creating a vlookup on 1 column only.

Can you confirm? If I'm right I can give you an answer.

Giz

"MartinW" wrote:

Ok Gary now I can understand your problem, unfortunately, I can't help you
other than to say I think it should be able to be done. Hopefully it won't
be long before one of the resident Gurus drops by and enlightens us both.

Cheers
Martin