1

I have to calculate "points" for each row based by Category and Listings

enter image description here

and I can fetch data from this table below.

enter image description here

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

3

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)),)))

enter image description here

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.

Sample data

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.

2
  • 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
    – Vylix
    Jun 5 '17 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.