CODEWITHSUNDEEP
excelvba
Siraj Samsudeen
Siraj Samsudeen

Reputation: 1694

Excel - How to add error handling to an array formula using OFFSET

I am populating a number of columns based on a lookup using OFFSET and MATCH. I want to add error handling so that the appropriate values are displayed in each of these columns - how can I do this in Excel and in Excel VBA?

Here are the details of the formula that I use:

I enter this formula as an array formula for the entire range B2:E100

=OFFSET(CustomerMaster!$A1,MATCH(A2,customerNumberList,0)-1,2,1,4)

I use the customerNumber in column A2 to fetch a 4 columns related to the customer in another sheet - CustomerMaster.

Now, if there is an error, I want all the 4 cells to have some value like "Unknown".

Upvotes: 0

Views: 2572

Answers (1)

enderland
enderland

Reputation: 14135

Something like the following

=IFERROR(OFFSET(CustomerMaster!$A1,MATCH(A2,customerNumberList,0)-1,2,1,4),"Unknown")

will work (at least if you have Excel 2007 or later).

Upvotes: 1

Related Questions