On Thu, 21 May 2009 23:56:01 -0700, Dingerz
wrote:

I have got a few more columns in my formula.

=IF(ISERROR(MATCH(A25,G:G,0)),IF(ISERROR(MATCH(A2 5,H:H,0)),IF(ISERROR(MATCH(A25,I:I,0)),IF(ISERROR( MATCH(A25,J:J,0)),IF(ISERROR(MATCH(A25,K:K,0)),IF( ISERROR(MATCH(A25,L:L,0)),"NotFound",6),5),4),3),2 ),1)

But swtill i am limited.

basically it looks like this

1 2 3 4
5
A dhhs fgkgj hhhh kkkk hghhg
B ggjnk jghn rfh hhh ccfg
etyiikn



This continues for alot of colums and rows. Now I have another bunch of data
which iam trying to see if is in the data above. If so what column is it in.

IE. Cell G9 (Another set of data) contains hhhh. I want it to tell me that
there is a duplicate item in column 3.

At the moment as you can see from my formula im checking row by row but iam
limited by the number of rows i can check

To return the Column number of data (in A25) in your data table (Name the
range: Tbl ), try this **array-entered** formula:

=MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl,0,0,,1)= A25)+
(OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25)+
(OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25)+
(OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0)

This formula must be **array-entered**:

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

Note that with versions of Excel prior to 2007, you cannot reference an entire
column in an array formula.

Also note that I used a NAME'd range for your data table. This will make
future maintenance of this formula much simpler than hard-coding the ranges.
Tbl could even be defined as a dynamic range, so as to avoid "oversizing" the
range to search.

This will return #N/A if the data in A25 is not found in Tbl. So you could
also do something like:

Pre 2007: (**array-entered**)

=IF(ISNA(MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl, 0,0,,1)=A25)+
(OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25)+
(OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25)+
(OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0)),"Not Found",
MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl,0,0,,1)=A 25)+
(OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25)+
(OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25)+
(OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0))

Or, if you have 2007+:

=IFERROR(MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl, 0,0,,1)=A25)+
(OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25)+
(OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25)+
(OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0),"Not Found")

--ron