MATCH not working? (2000sp4)

Topic

New Reply

Trying to use match to look up sample numbers in a series of nxn grids in different sheets, and not having much luck. Always seems to return #N/A.

Quite possible that none of the numbers in the list, apart from the last one, are in the lists, but the last number has been cut and pasted from one of the sheets and still all the MATCH functions return #N/A.

What am I doing wrong?

Thanks

Reply | Quote

Replies

Post deleted by shades

Reply | Quote

Yet it was made with the function wizard and not hand edited!

Reply | Quote

Match only works on a one column array. SInce it only returns a single number, there is no way for it to return a row number and a column number from a two dimensional array. If you can’t arrange your lookup data into a single column, then you will either have to write a User Defined Function to do the lookup and return whatever value you want (what do you want returned?), or you will have to code a complicated IF statement that looks in each column independently and then somehow calculates whatever return you want.

Reply | Quote

Was afraid it might be something like that – I had thought that MATCH was more flexible than V or Hlookup, but obviously in different ways.

The data is in the nxn array for a reason, and needs to stay that way.

Looks like I’ll have to incorporate some VBA finding. How annoying.

Interestingly online help doesn’t hint at the unidimensionality of MATCH:

“Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array can be an array or an array reference.”

Thanks

Reply | Quote

Hi Mark,
What are you actually trying to return as a value? (if you just need to know if it’s there, you can use COUNTIF)

Edit:
If you actually wanted the address of the matching cell, if any, then you could use something like this for Plate 4 and adapt for the other three:
=IF(COUNTIF(‘plate4 grid’!$B$4:$M$23,Sheet4!$B2)=0,””,ADDRESS(SUMPRODUCT((‘plate4 grid’!$B$4:$M$23=Sheet4!$B2)*ROW(‘plate4 grid’!$B$4:$M$23)),SUMPRODUCT((‘plate4 grid’!$B$4:$M$23=Sheet4!$B2)*COLUMN(‘plate4 grid’!$B$4:$M$23))))

HTH

Reply | Quote

Why do I always need to be reminded of the existence of CountIf?!?!?!?!?

Thanks very much, some modification of your suggestion did exactly what I needed.

Reply | Quote