Return a value from one column using an array and a single row

[Travis Freed]

Thanks for looking at my question.


I have an an array of heights associated with several tree species, I want to transform these values into an integer (1-6). I have a large table of data with hundreds of individual trees with a species name (e.g. white fir) and height (0-300) and I want to assign each into one of these 6 classes. Each species has unique breakpoints for each class, so each column header hasa species name with the unique breakpoints in the 6 rows below it, there are 5 columns of species and 1 column with the 6 classes. I want to return the 1-6 class from some kind of vlookup or index match combination, but can't figure it out with my limited abilities

Thanks for any help you can provide!

I want to return the first column; the lookup values are the species (e.g. SP) and then the values in that column (i.e. 0 46 60 84 93 300)

SP WS PB PP DF
1 300 300 300 300 300
2 93 61 300 60 300
3 84 46 300 46 86
4 60 32 50 30 65
5 46 24 26 22 49
6 0 0 14 0 34

 

[Travis Freed]

solution was provided to me on another site:

=INDEX($B$2:$B$7,MATCH(C12,INDEX($C$2:$G$7,,MATCH(B12,$C$1:$G$1,0)),1))