IF and VLOOKUP - how efficient?

[anthonyg]

Hi I have a big dataset and each item needs to be classified as either
"active" or "inactive". By default lines are inactive, unless they are
contained within a subset that is listed in a separate database, where
individual lines are classified active/inactive, in which case they are
classifed the same as the separate database.

I'm currently using the following formula in my classification column:

IF(ISNA(VLOOKUP(A1,[Active
Analysis]Data!A:Z,26,FALSE)),"Inactive",VLOOKUP(A1,[Active
Analysis]Data!A:Z,26,FALSE))

My question is, when I use this formula does Excel execute the same lookup
twice, or is it smart enough to do the lookup once and use the result twice?
Either way it certainly seems to take a long time. Is there a more
efficient method?

Thanks

 

[Bob Phillips]

Yes it will if it is found. Put the VLOOKUP formula in another cell then
test

=IF(ISNA(cell_ref),"Inactive",cell_ref)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave F]

It executes it twice. XL 2007 has the IFERROR function which has the format
=IFERROR(VLOOKUP(....),[else]) which is more efficient.

Dave

 

[Niek Otten]

If the item is not in the list, it will execute the VLOOKUP once, otherwise it will execute it twice.
You could place the VLOOKUP in a separate cell (column) and test that; that would certainly speed things up, but no more that
twice.
If you would be able to sort the range in which you look up, you could improve speed significantly by omitting the 4th argument of
VLOOKUP. In that case you would have to test for presence of the item yourself, but for large ranges it might well be hundreds of
times faster.
See:
http://www.decisionmodels.com/optspeede.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi I have a big dataset and each item needs to be classified as either
| "active" or "inactive". By default lines are inactive, unless they are
| contained within a subset that is listed in a separate database, where
| individual lines are classified active/inactive, in which case they are
| classifed the same as the separate database.
|
| I'm currently using the following formula in my classification column:
|
| IF(ISNA(VLOOKUP(A1,[Active
| Analysis]Data!A:Z,26,FALSE)),"Inactive",VLOOKUP(A1,[Active
| Analysis]Data!A:Z,26,FALSE))
|
| My question is, when I use this formula does Excel execute the same lookup
| twice, or is it smart enough to do the lookup once and use the result twice?
| Either way it certainly seems to take a long time. Is there a more
| efficient method?
|
| Thanks

 

[T. Valko]

Another way:

Instead of duplicating the lookup and testing for an error, test to see if
the value is present:

=IF(COUNTIF(Data!A:A,A1),VLOOKUP(A1,Data!A:Z,26,0),"Inactive")

Biff

 

[anthonyg]

Thanks for all the replies and the link to decision models. I followed the
instructions there and it is much faster now.

Thanks again.

 

[Aladin Akyurek]

IsNumber/Match is faster than CountIf though.

Another way:

Instead of duplicating the lookup and testing for an error, test to see if
the value is present:

=IF(COUNTIF(Data!A:A,A1),VLOOKUP(A1,Data!A:Z,26,0),"Inactive")

Biff

Hi I have a big dataset and each item needs to be classified as either
"active" or "inactive". By default lines are inactive, unless they are
contained within a subset that is listed in a separate database, where
individual lines are classified active/inactive, in which case they are
classifed the same as the separate database.

I'm currently using the following formula in my classification column:

IF(ISNA(VLOOKUP(A1,[Active
Analysis]Data!A:Z,26,FALSE)),"Inactive",VLOOKUP(A1,[Active
Analysis]Data!A:Z,26,FALSE))

My question is, when I use this formula does Excel execute the same lookup
twice, or is it smart enough to do the lookup once and use the result
twice?
Either way it certainly seems to take a long time. Is there a more
efficient method?

Thanks