CODEWITHSUNDEEP
excel
Tsaukpaetra
Tsaukpaetra

Reputation: 588

Return first value in a row that starts with a certain string

I have an Excel workbook that calculates some validation criteria on a row-by-row basis. I would like to create a column that gets the first value of a failed criteria, or display nothing if validation passed. The trouble is, most examples rely on the idea that the data to be found is in a column, not in a row, and for some reason I can't get HLOOKUP to work in this instance.

For example, I have as row headers:

ID   Balance   Requests   Failed_LowBalance   Failed_MaxRequestsExceeded

And data:

0    4.00      4          =IF(B2<10,"Failed: Low Balance","")    =IF(C2>5,"Failed: Max Requests Exceeded","")

As shown, column D would only have a value if column B (Balance) is less than ten, and E would have a value if C was greater than five.

Ideally I would just have the first instance where the cells D and E may have a value, and I can emulate that by using a nasty cascading IF statement (ie IF(D2<>"",D2,IF(E2<>"",E2,"")) ), but I'd rather not for obvious reasons.

I tried using HLOOKUP like so, but it doesn't work very well at all:

=HLOOKUP("*",D2:E2,1,0)

Upvotes: 1

Views: 1770

Answers (2)

MikeD
MikeD

Reputation: 8941

Instead of displaying clear text error messages, you can choose to display numeric values (1, 2, 3, ...) as the meaning is clear via the column headers. Then your first error is the =MIN(...) of all error conditions in the row.

On top of that you can have a list of error numbers and clear text descriptions, which provides you with the source of a =VLOOKUP(...) besides =MIN(...)

Upvotes: 1

barry houdini
barry houdini

Reputation: 46331

You can use this formula with a wildcard if the string will always start with "Failed"

=HLOOKUP("Failed*",D2:E2,1,0)

...and you can add IFERROR function to return some text value if "Failed" doesn't exist, e.g.

=IFERROR(HLOOKUP("Failed*",D2:E2,1,0),"")

Upvotes: 4

Related Questions