0

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

Improve this question
1
  • Use a Select Case statement if you are using VBA. Commented Aug 29, 2019 at 1:47

1 Answer 1

Reset to default
2

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@chrisneilsen Good point. That can also be done for the 261 entry. I will edit my response with credit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.