Problem

1. How to find value under index match with 2 dimensions lookup
2. Using index match with greater than and equal to criteria
3. Adding column without changing data range in index match formula

Existing data

Table 1: 
*numbers under purchasing no. are the purchasing quantity of the item.

| Column A | Column B          | Column C          | Column D          |
| -------- | ----------------  | ----------------  | ----------------  |
| Item     | purchasing no.1   | purchasing no.2   | purchasing no.3   |
| apple    | 2                 | 10                | 2                 |
| banana   | 5                 | 2                 | 7                 |



Table 2
| Column A | Column B | Column C       |
| -------- | -------- | -------------- |
| Item     | order    | purchasing no. |
| apple    | 7        | value needed   |

What I want

1. After entering "apple" in 'Table 2'!A2 , and "7" in 'Table 2'!B2 , 'Table 2'!C2 should give me "purchasing no.2" as value I need. Since the 7th apple is bought under purchasing no.2.
2. Even if Column E is added afterwards in Table 1, formula in 'Table 2'!C2 should stay the same and output the correct purchasing no.

Here is the formula I wrote in 'Table 2'!C2

=index('Table 1'!B1:D1,match(A2,'Table 1'!A2:A,0),match(B2,indirect(concatenate("'Table 1'!B",match(A2,'Table 1'!A:A,0),":","D",match(A2,'Table 1'!A:A,0))),-1))

Problem

1. If I enter "7" in 'Table 2'!B2 , 'Table 2'!C2 will give "purchasing no.2", which is correct. However, if I enter "12" in 'Table 2'!C2 , 'Table 2'!C2 will display #N/A, which should be "purchasing no.2" as well, since 12th apple is bought under purchasing no.2.
2. If I added column E in Table 1, 'Table 2'!C2 only gives value between 'Table 1'!B1:D1 .

*note: using index & match is not necessary, if there's other formula can avoid the encountered problems

CodePudding user response：

try this out:

=xlookup(AB9,SCAN(,FILTER(B:Z,A:A=AA9),LAMBDA(ac,cv,ac cv)),B1:Z1,,1)

CodePudding user response：

You need cummulative sums to find the place of that last "apple". You can set it by inserting SCAN in your formula:

SCAN(0,INDEX('Table 1'!B2:Z,match(A2,'Table 1'!A2:A,0)),LAMBDA(a,v,a v))

And for finding that higher value you can benefit from XLOOKUP that will return the header corresponding to that match:

=IFNA(XLOOKUP(B2, SCAN(0,INDEX('Table 1'!B2:Z,match(A2,'Table 1'!A2:A,0)),LAMBDA(a,v,a v)), 'Table 1'!B1:Z1,,1))

And finally if you want it to work for the whole column just by inserting one only formula in C2 you can use MAP:

=MAP(A2:A,B2:B,LAMBDA(av,bv,
IFNA(XLOOKUP(bv,
  SCAN(0,INDEX('Table 1'!B2:Z,match(av,'Table 1'!A2:A,0)),LAMBDA(a,v,a v)),
  'Table 1'!B1:Z1,,1))))