I need to run an if then else statment that tests thru 420 values of data (420) times ? Does anyone know if there is any spreadsheet program out there that has a much larger cap than the 7 limit excel has for and if then else statement ?
Steven,
Maybe you could expand on your needs a little bit. What was mentioned by another poster may work but then we don't know the layout of your data. Based on the fact you mentioned 420 values indicates to me that the data sits in rows and not columns (>255).
Is the data in the same column or multiple columns? What do you want to do with the test? For example you may need to abandon vLookup if the data you want to return is not located where vlookup needs it:
From help:
'Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.
The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.'
You may want to use another method, or in fact need to use another method but we don't know that based on your post yet.
Another option is to use a macro. This may not be the most efficient based on your needs, but it may also provide the flexibility. If you actually need an If/Then solution you are not limited to 7 If/Then combinations, but then you may really be after using 'Select Case' if you go the VBA route, again based on your needs. You can also use find/match.
Give us more of a hint on what you really need to do and the format of your data to be searched, found, matched, etc.
Perry
