Error handling with Index-Match nested functions

[n8te]

Hello Everyone,

I have an issue which I'm sure won't be a very difficult one to resolve. I'm using in a table (t_b) an index-match function to return values that are stored in another table (t_a). Such as:

 {=INDEX(t_a[Price];MATCH(1;([@[P_PartialName]]=t_a[Partial Name])*([@[units]]=t_a['# units]);0))}

It works fine. However, this famous table A is a table that should be populated. As users begins with the workbook, this table only has one row (and they can then add more). When the table only contains one row, then the index/match does not work. How can I still force Excel to find the only value contained in the correspondent column?

Thanks in advance everyone.

 

[duggie33]

Hi *n8te,

Can you add a check to how many rows are in the table before trying the index/match? For instance, put the INDEX/MATCH inside of an IF statement that does a COUNT OR COUNTA of t_A{Price]. If the count is greater than 1, INDEX/MATCH, else returns the only row or something else. I am not sure I follow exactly the structure of your data so you may have to adjust.

An alternative to index/match with two criteria is the FILTER function. It has quickly become my favorite.

Hope that helps,

Doug

 

[n8te]

Thanks. This was indeed an easy solution. I don't know why I didn't tought about it. Thanks anyway

 

[duggie33]

No problem, glad I could help.

Doug