Index Match skip zero value

0
votes

I need some help with my issue. I'm dealing with a lot of data. I'm looking for a value in a field that is not zero. in A1 value is attorney fee. So my data sheet has attorney fee listed three times. For example: Attorney Fee $0, Attorney Fee $0, Attorney Fee $1,500. How do I return the value in the 6th cell. Index/Match is only returning the value in the 2nd column, which is zero.

Index/Match and Vlookup. 

=INDEX(data!V2:FO2,,MATCH($A$1,(data!V2:FO2),0)+1).

$A$1 is the attorney fee text. So it's pulling the next column, but I don't know how to return the value of the highest match.

1
excelexcel-formula
Welcome to SO. please see How to create a Minimal, Complete, and Verifiable example - Foxfire And Burns And Burns

1 Answers

0
votes

Not sure this is what your data looks like but:

enter image description here

Formula in C1 translates to:

{=MAX(IF(A1:A3="Attorney Fee",B1:B3))}

Notice it's an array formula and should be entered through CtrlShiftEnter

Also note, column be are currency values, if you left this as text it will return 0.