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

 

[MARK858]

Sorry for the confusion Aladin, I put the formulas into Y & Z purely to demonstrate the results I was getting side by side. It was not where they go in the actual spreadsheet. This result is referring to posting #4.

On the formulas in posting #8 I am getting a too many arguments error for the first formula and a "formula contains an error" on the second formula.
I have entered the first as

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

and the second as

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

 

[Aladin Akyurek]

Sorry for the confusion Aladin, I put the formulas into Y & Z purely to demonstrate the results I was getting side by side. It was not where they go in the actual spreadsheet. This result is referring to posting #4.

On the formulas in posting #8 I am getting a too many arguments error for the first formula and a "formula contains an error" on the second formula.
I have entered the first as

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

and the second as

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

Darn it... Apologies...

1. T() is missing its closing paren. Corrected:

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

2. The 2nd IF() is missing its closing paren. Corrected:

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

Hi Aladin I had to change in the second formula

LOOKUP(A2,Sheet3!A:A,Sheet3!I:I));

to

LOOKUP(A2,Sheet3!A:A,Sheet3!I:I)),

and then it gave the results I am looking for.

Just to clarify that my understanding is correct you believe this will be more efficient than the dynamic named range solution in Mike's previous posting?

Obviously I realise that your code must have Column A sorted and Mikes is a solution if it isn't.

P.S. could you explain what the "T" is in the first formula

 

[Aladin Akyurek]

Hi Aladin I had to change in the second formula

LOOKUP(A2,Sheet3!A:A,Sheet3!I:I));

to

LOOKUP(A2,Sheet3!A:A,Sheet3!I:I)),

and then it gave the results I am looking for.

Just to clarify that my understanding is correct you believe this will be more efficient than the dynamic named range solution in Mike's previous posting?

Yes. Even without resorting to a dynamic named range.

Obviously I realise that your code must have Column A sorted and Mikes is a solution if it isn't.

Yes, the set up requires sorting on column A in ascending order.

P.S. could you explain what the "T" is in the first formula

T(A2)

returns a blank when A2 is empty/blank or houses a numeric value or a logical value; If A2 houses a text value of non-zero length, it returns that text value as is.

 

[MARK858]

Thank you Aladin for your help and hard work on this thread. I also thank you for helping me learn a bit more (not just for your explanation of T(A2) but for teaching me a bit more with the formula itself).

I also thank Mike and Cindy again for their earlier postings as they too have improved my knowledge , your efforts are appreciated.

 

[FastExcel]

Sorting the data and using Aladdin's method (using IF to check if the value you get when you lookup the lookup column itself equals the original lookup Value) will be orders of magnitude faster than any other solution.

 

[MARK858]

Thanks for your input Charles. It definitely has been when testing the various options today