Is this the most efficient formula

[MARK858]

Hi all,
I have the Vlookup formula below. Now it is working correctly but the question I have is this the most efficient way of doing this?

It is obviously performing 3 Vlookups to get the desired result (i.e. turning all errors and value 0 to "Not Set") and this gives me concerns over whether it the most efficient way of achieving it.

Unfortunately it does need to work with 2003.

Any pointers anyone can give would be much appreciated.
Formula
=IF(ISERROR(VLOOKUP(A2,Sheet3!A:M,9,0)),"Not Set",IF(VLOOKUP(A2,Sheet3!A:M,9,0)=0,"Not Set",VLOOKUP(A2,Sheet3!A:M,9,0)))

 

[mikerickson]

You could use this function
=IF(ISERROR(VLOOKUP(A2,Sheet3!A1:M1000,9,0)), "Not Set", VLOOKUP(A2,Sheet3!A1:M1000,9,0))

and format the cell with the custom format General;-General;"Not Set"

The use of a not-whole-column reference will speed things up.

 

[Cindy Ellis]

If you need the value in the cell to actually contain the text "Not Set". not just as the result of formatting, you could use the following, which replaces one vlookup with a countif, and gets rid of one nested if. I really don't know if there would be any noticeable difference in efficiency; it's just a different way to accomplish the same end result.

=IF(OR(COUNTIF(Sheet3!A:A,Sheet1!A2)=0,VLOOKUP(Sheet1!A2,Sheet3!A1:M1000,9,0)=0),"Not Set",VLOOKUP(Sheet1!A2,Sheet3!A1:M1000,9,0))

If this formula will be copied down or across to multiple cells, be sure to use $ with the row and column in the VLOOKUP's (e.g. $A$1:$M$1000).

Hope that helps,

Cindy

 

[Aladin Akyurek]

Hi all,
I have the Vlookup formula below. Now it is working correctly but the question I have is this the most efficient way of doing this?

It is obviously performing 3 Vlookups to get the desired result (i.e. turning all errors and value 0 to "Not Set") and this gives me concerns over whether it the most efficient way of achieving it.

Unfortunately it does need to work with 2003.

Any pointers anyone can give would be much appreciated.
Formula
=IF(ISERROR(VLOOKUP(A2,Sheet3!A:M,9,0)),"Not Set",IF(VLOOKUP(A2,Sheet3!A:M,9,0)=0,"Not Set",VLOOKUP(A2,Sheet3!A:M,9,0)))

On Excel 2007 or later and a text result is expected...

=IFERROR(T(VLOOKUP(A2,Sheet3!A:M,9,0)),"Not Set")

On all versions and a text result is expected...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"Not Set",T(VLOOKUP(A2,Sheet3!A:M,9,0))))

If the table area in A:M on Sheet3 is or can be sorted on column A, a much faster option is possible.

Also, if whole column references are not necessary, try the use exact ranges. Otherwise, a dynamic named range would be appropriate.

 

[MARK858]

Thanks for the reply Mike.
I am afraid I will have trouble with not using the whole-column reference as the sheet is pulled in from the company system via a text file using a macro and number of rows varies twice weekly (Basically it is a listing of all the product lines in the company and last time I can remember it stood at 13800+ product codes. By April each year it normally stands at approx. 26000) and so I will have to set it at 30000 to play safe.
Is it worth it with a number so high?
The formula in the first posting is in a macro with a fill down to last row in a results sheet. The source sheet is again pulled in via a macro
What I am thinking though is using a find and replace code on the source sheet to change the 0 values to "Not set" at that stage, which will cause it to appear as an error in the result sheet but at least that means there will be only the 2 Vlookups needed on the errors.
Do you think this method will give any efficiency saving or would it negligible?

 

[mikerickson]

Yes it is worth setting it that high.
Its also worth using a dynamic named range, like:

Name: dataRange
RefersTo: Sheet3!$M$1:INDEX(Sheet3!$A:$A, COUNTA(Sheet3!$A:$A), 1)

with the formulas altered like
=IF(ISERROR(VLOOKUP(A2,dataRange,9,0)), "Not Set", VLOOKUP(A2,dataRange,9,0))

If you have blank rows in Sheet3!A:A, the defintion of the named range will need to be altered.

 

[MARK858]

Cindy, thanks for your response I will give it some testing to see if it is more or less efficient but probably not until tomorrow at work when i can test it on somebodies 2003 version

Aladin, thanks for your response. Unfortunately I do need the values from the cells not involved in the "Not Set" criteria. the not set text with the 0 is basically there to take it out of a future less than formula. On your question of the sorting on Sheet3. That wouldn't be a problem.

Mike, I am just reading through your latest suggestion. I will post back once I have given it a try

Again, thanks for all the replies

 

[Aladin Akyurek]

...
Aladin, thanks for your response. Unfortunately I do need the values from the cells not involved in the "Not Set" criteria. the not set text with the 0 is basically there to take it out of a future less than formula.

I don't understand this part. Are you referring to using the T() function?

On your question of the sorting on Sheet3. That wouldn't be a problem.
...

Well, this is good news...

Either:

=IF(LOOKUP(A2,Sheet3!A:A)=A2,
     T(LOOKUP(A2,Sheet3!A:A,Sheet3!I:I),"Not Set")

Or:

=IF(LOOKUP(A2,Sheet3!A:A)=A2,
     IF(LOOKUP(A2,Sheet3!A:A,Sheet3!I:I)="",
        "Not Set",
        LOOKUP(A2,Sheet3!A:A,Sheet3!I:I);
     "Not Set")

 

[MARK858]

Mike, I have tried your latest posting manually and it seems to be working well. I have to work out how to put the dynamic named range into a macro but I am sure the recorder will get me there.

Aladin

I don't understand this part. Are you referring to using the T() function?

What I was referring to is the result I was looking for was a result as per Column Y but the solution you gave was as per column Z. I.e. the numbers were not there.

Excel Workbook
	Y	Z
1	*	*
2	112	*
3	112	*
4	720	*
5	720	*
6	Not Set	Not Set
7	Not Set	Not Set
8	Not Set	Not Set
9	Not Set	Not Set
10	Not Set	Not Set
11	Not Set	Not Set
12	Not Set	Not Set
13	Not Set	Not Set
14	Not Set	Not Set
15	300	*
16	300	*
Sheet2

I am going with Mikes solution (as long as I can convert it to VBA) as I think that is going to be the way to go.

I thank all three of you for your work on this (In particular Mike for what is hopefully the final solution). As always the help given on this site is invaluable

 

[Aladin Akyurek]

Mike, I have tried your latest posting manually and it seems to be working well. I have to work out how to put the dynamic named range into a macro but I am sure the recorder will get me there.

Aladin

What I was referring to is the result I was looking for was a result as per Column Y but the solution you gave was as per column Z. I.e. the numbers were not there.

...

I really understand this at all. Neither post #4 nor post #8 has anything on column Y or column Z. Moreover, the latter gives you the most efficient approach if the sorting as you said is not a problem.