IF and VLOOKUP - how efficient?
 

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

IF and VLOOKUP - how efficient?
 

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)



"anthonyg" wrote in message
...
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

IF and VLOOKUP - how efficient?
 

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

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"anthonyg" wrote:

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

IF and VLOOKUP - how efficient?
 

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

"anthonyg" wrote in message ...
| 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

IF and VLOOKUP - how efficient?
 

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" wrote in message
...
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

IF and VLOOKUP - how efficient?
 

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

Thanks again.


"Niek Otten" wrote:

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

"anthonyg" wrote in message ...
| 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

IF and VLOOKUP - how efficient?
 

IsNumber/Match is faster than CountIf though.

T. Valko wrote:
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" wrote in message
...
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