CODEWITHSUNDEEP
excelvbaloops
Hilmi
Hilmi

Reputation: 43

Is there any ways to replace nested if in excel-vba?

Hye I am new to excel vba so don’t how to use it efficiently, I got almost 50,000 row of data for the TestID. I want to do some dynamic looping to determine the TestType. Let say if the TestID is 001 to 100 the TestType is TEST A,if TestID is 101 to 220 the TestType is TEST B, if TestID is 221 to 260 the TestType is TEST C.

Is there a way to do this without using nested if using excel vba?

I using nested if to do this but the data is too many

enter image description here

=IF(ISNUMBER(SEARCH("001",[@TestID])),"Test A",IF(ISNUMBER(SEARCH("002",[@TestID])),"Test A",IF(ISNUMBER(SEARCH("003",[@TestID])),"Test A","")))

If the TestID is 001 to 100 the TestType is TEST A,if TestID is 101 to 220 the TestType is TEST B, if TestID is 221 to 260 the TestType is TEST C

Upvotes: 0

Views: 77

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

A common, non-VBA method of handling that problem is with a lookup table.

This assumes your TestID values are numeric and not text strings.

For your conditions, the formula might be:

=VLOOKUP(A2,{1,"TEST A";101,"TEST B";221,"TEST C";261,"Unspecified"},2)

enter image description here

As pointed out by @chrisneilson, if you want to return a blank for values <1 or >=261, you can modify the formula:

=VLOOKUP(A2,{0,"";1,"TEST A";101,"TEST B";221,"TEST C";261,""},2)

Upvotes: 2

Related Questions