Vlookup with sun tables

I have to calculate "points" for each row based by Category and Listings and I can fetch data from this table below. For example, the first row is:

Category: Hardlines

Listings: 165

the outcome for the "points" columns should be -> Points: 2

Do you know how to do it in excel? I can't change the structure of the source table

Use a formula like this one:

=INDEX(B:D,MATCH(H2,A:A,0)+2,MATCH(G2,INDEX(--(LEFT(INDEX(B:D,MATCH(H2,A:A,0)+1,0),FIND("-",INDEX(B:D,MATCH(H2,A:A,0)+1,0))-1)),))) First, normalize your reference table. It is easier to do this when you don't have to reference from range of values (1-19).

Hardlines   Tier 0  | 150   | 2
Hardlines   Tier 1  | 20    | 1
Hardlines   Tier 2  | 1     | 0.5
Softlines   Tier 0  | 100   | 2
Softlines   Tier 1  | 20    | 1
Softlines   Tier 2  | 1     | 0.5
Media       Tier 0  | 500   | 2
Media       Tier 1  | 20    | 1
Media       Tier 2  | 1     | 0.5

Please note that the ranges of value is sorted DESCENDING (150 > 20 > 1)

After that, insert this formula into your Points column.

=INDEX(\$N\$1:\$N\$9, MATCH(1, (B1 >= \$M\$1:\$M\$9) * (C1 = \$K\$1:\$K\$9), 0))

and press Ctrl+Shift+Enter instead of Enter to insert the array formula. The formula is looking for Points referenced in column N, which matching two criteria :

1. B1 >= \$M\$1:\$M\$9 The value on lookup is higher than the referenced values in column M.

2. C1 = \$K\$1:\$K\$9 The value on lookup is matching the referenced string in column K.

• Thank you, Unfortunately I can't change the structure of the source table Jun 5 '17 at 15:30
• @user1028100 you don't have to change the structure, but instead create a helper table to help you simplify the formula. See my screenshot, the source table still exist, but there is additional helper table to simplify the formula used Jun 5 '17 at 18:27