Index and Match Based on Multiple Criteria

**duranbeaz** · 05-20-2009, 04:24 PM

Alright my last thread was a little fruitless since I had a working formula already. Here is a better problem for you all

I'm using Index and Match to find the value of the first number in a row and then to display a corresponding value in the same column, different row.

Here is the formula I found to just get the value of the first number:

=INDEX($G29:$QC29,MATCH(TRUE,INDEX($G29:$QC29>0,0,0),0))

The problem is my result is 5* since I have some numbers with astericks beside them. I want to find the first actual number, not just non-blank value. If I try to put in another criteria to say >0 but <=25 (highest numbers go is 25) I get an error every time.

This is the formula I'm using right now that works for the rows that don't have an *value as the first value in the row:

=INDEX($G$2:$QC$2,,MATCH((INDEX($G28:$QC28,,MATCH(TRUE,$G28:$QC28>0,0))),Ranks!$G28:$QC28,0))

It still works but it just gives me the value in row 2 of the column with the *. In need it to skip that, find the first actual number (in this case 16, but it can vary) and then give me the row 2 value of that column.

I hope I was specific enough this time! Thanks for your help again. I really do appreciate it.

Johnathan

**DonkeyOte** · 05-21-2009, 03:09 AM

Perhaps, if I've understood... ?

Please Login or Register  to view this content.

This assumes you don't need to restrict to numbers > 0 just find a number... (some debate if you're using row 28 or 29 your sample formulae vary)

(where the INDEX is a vector (ie 1 row or 1 column) you don't need to specify both row and column... ie INDEX(A1:A10,4) will return A4 and INDEX(A1:Z1,4) will return D1 ... if it is a matrix range then you should specify row & column)