Error Handling - Lookup First "Error" in a table

[bs0d]

I've got a pseudo form that I made using cells in a worksheet that must check for valid user input before passing data along to a query.

I've built a table of potential errors with the logic in the next column (checking for the error).

I'd like to look for "Error" within the range of errors, and return the text describing the first error encountered.

If I use lookup or vlookup, i get the N/A error. I'm certain theres a fancy way to do this that I'm not aware of.

Example data:

Error #, description,....... logic
1........no name entered... OK
2........invalid zip code......OK
3........no address given....ERROR
4........enter phone #.......OK
5........enter something.....ERROR

I would expect the formula to return the no address error as it is first. Using count-if i can count the # of errors

 

[T. Valko]

I've got a pseudo form that I made using cells in a worksheet that must check for valid user input before passing data along to a query.

I've built a table of potential errors with the logic in the next column (checking for the error).

I'd like to look for "Error" within the range of errors, and return the text describing the first error encountered.

If I use lookup or vlookup, i get the N/A error. I'm certain theres a fancy way to do this that I'm not aware of.

Example data:

Error #, description,....... logic
1........no name entered... OK
2........invalid zip code......OK
3........no address given....ERROR
4........enter phone #.......OK
5........enter something.....ERROR

I would expect the formula to return the no address error as it is first. Using count-if i can count the # of errors

How are the error numbers generated? By a formula? If so, what does one of these typical formulas look like?

I'm thinking that the error code is numeric and the error number in your table is TEXT (or vice versa).

See this:

http://contextures.com/xlFunctions02.html

And pay close attention to this section of the page:

http://contextures.com/xlFunctions02.html#Trouble

 

[Norie]

How did you use the lookups?

You should be able to do what you want with something like this:

=VLOOKUP(A1, Errors!A2:C6, 2, 0)

=VLOOKUP(A1, Errors!A2:C6, 3, 0)

A1 has the error code and the list of errors is in columns A to C on a worksheet called Errors.

The first formula will return the description, the 2nd the logic.

 

[Aladin Akyurek]

I've got a pseudo form that I made using cells in a worksheet that must check for valid user input before passing data along to a query.

I've built a table of potential errors with the logic in the next column (checking for the error).

I'd like to look for "Error" within the range of errors, and return the text describing the first error encountered.

If I use lookup or vlookup, i get the N/A error. I'm certain theres a fancy way to do this that I'm not aware of.

Example data:

Error #, description,....... logic
1........no name entered... OK
2........invalid zip code......OK
3........no address given....ERROR
4........enter phone #.......OK
5........enter something.....ERROR

I would expect the formula to return the no address error as it is first. Using count-if i can count the # of errors

Somewhat unclear... Care to elaborate a bit referring to your "Example data"?

 

[bs0d]

the error #'s are static values (I just numbered them). Where you see "Error" or "Ok" - these cell contain the logic that detects for the error. I cannot predict what ones will be flagged- that is based on the user input. But I'd like to show the first "error" that is encountered starting from the top, and display the error text in the column next to the logic.

I tried lookup("Error", C1:C5, B1:B5) -assuming "C" contains the logic (Error or OK), and "B" contains the error description.

 

[Norie]

So is this some sort of log rather than a list of error types/numbers and you want to find all the rows with column C has ERROR.

 

[Aladin Akyurek]

the error #'s are static values (I just numbered them). Where you see "Error" or "Ok" - these cell contain the logic that detects for the error. I cannot predict what ones will be flagged- that is based on the user input. But I'd like to show the first "error" that is encountered starting from the top, and display the error text in the column next to the logic.

I tried lookup("Error", C1:C5, B1:B5) -assuming "C" contains the logic (Error or OK), and "B" contains the error description.

Still hard to visualize... Looking for:

=INDEX(DescriptionRange,MATCH("ERROR",LogicRange,0))

perhaps?

 

[T. Valko]

the error #'s are static values (I just numbered them). Where you see "Error" or "Ok" - these cell contain the logic that detects for the error. I cannot predict what ones will be flagged- that is based on the user input. But I'd like to show the first "error" that is encountered starting from the top, and display the error text in the column next to the logic.

I tried lookup("Error", C1:C5, B1:B5) -assuming "C" contains the logic (Error or OK), and "B" contains the error description.

Ok, now I think I understand.

Try this...

Book1
	A	B	C
2	1	no name entered	OK
3	2	invalid zip code	OK
4	3	no address given	ERROR
5	4	enter phone #	OK
6	5	enter something	ERROR
7	_	_	_
8	_	no address given	_
Sheet1

This formula entered in B8:

=INDEX(B2:B6,MATCH("error",C2:C6,0))