Running out of Functions

[Dingerz]

Hi I want to do make a long function with lots of IF's but have used the max
amount of functions. Can someone help? Perhaps another method or even how to
translate my code into a macro ( I dont know much about macros or even how to
use them)

=IF(ISERROR(MATCH(A2,G:G,0)),IF(ISERROR(MATCH(A2,H:H,0)),"NotFound",$H$7),$G$7)..................................etc

 

[Jacob Skaria]

Are you trying to match the value in A2 to a range...

The below array formula will return a value greater than 0 for a match..
=MIN(IF(G1:J1000="Green",ROW(G1:J1000)))

If this post helps click Yes

 

[Dingerz]

Jacob,

That did not help Thanks anyway.

I have got a few more columns in my formula.

=IF(ISERROR(MATCH(A25,G:G,0)),IF(ISERROR(MATCH(A25,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

Regards,

Garrath

 

[Jacob Skaria]

Try the below in a new workbook and once done you should be able to work out
a formula...

Open a new workbook.
Enter a text; say "Dingerz" into any cell in the range of A2:Z10000

Enter the below formula in A1. Please note that this is an array formula.
Within the cell in edit mode (F2) paste this formula and press
Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you
can notice the curly braces at both ends like "{=<formula>}"

=MIN(IF(A2:Z10000="Dingerz",COLUMN(A2:Z10000)))

This should return the column number.....

If this post helps click Yes

 

[Ron Rosenfeld]

I have got a few more columns in my formula.

=IF(ISERROR(MATCH(A25,G:G,0)),IF(ISERROR(MATCH(A25,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)=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))

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

 

[Ron Rosenfeld]

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)=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))

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

Forget this. Jacob's response is much simpler. It could also be rewritten as
the **array-formula**:

=MIN(IF(A25=Tbl,COLUMN(Tbl)))

However, the number returned will be the absolute column number, and the column
number within Tbl.
--ron