Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
AlexaaAlexa0 la Simpatie.ro
 | Femeie
24 ani
Valcea
cauta Barbat
28 - 42 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 18 ani
Postari: 2308
|
|
Key words: LAMBDA INDEX MATCH VSTACK
I am trying to find a matching index in 2 ranges.
I want to use drop down lists to select the 2 lookup values and then return the index corresponding to both.
First lookup value is in cell E1 and first lookup range is in G3:G26.
Second lookup value is in cell F1 and second lookup range is in I3:I26.
This is to obtain the relative index corresponding to both ranges:
| =MATCH(1; (G3:G26=E1) * (I3:I26=F1); 0) |
LAMBDA function made with the above:
=LAMBDA(lookup_value_1;lookup_range_1;lookup_value_2;lookup_range_2;
MATCH(1; (lookup_range_1=lookup_value_1) * (lookup_range_2=lookup_value_2); 0)
)(E1; G3:G26; F1; I3:I26)
The value of my index obtained in the formula above is stored in cell G1.
So now I want to VSTACK those ranges like this:
- 1st: range from index to the end
- 2nd: range from start to index - 1
=VSTACK(
INDEX(G3:G26; G1):G26;
G3:INDEX(G3:G26; G1-1)
) |
Now, although a LAMBDA would seem pointless as I might not have this scenario ever again, here goes (because it's fun!):
=LAMBDA(lookup_value_1;lookup_range_1;lookup_value_2;lookup_range_2;vstack_range;
LET(
idx; MATCH(1; (lookup_range_1=lookup_value_1) * (lookup_range_2=lookup_value_2); 0);
VSTACK(
INDEX(vstack_range; idx) : INDEX(vstack_range;COUNTA(vstack_range));
INDEX(vstack_range; 1) : INDEX(vstack_range; idx-1)
)
)
)(E1; G3:G26; F1; I3:I26; I3:I26) |
* * * *
If you want the actual row number in the sheet instead of the relative index:
| =INDEX(ROW(G3:G26); MATCH(1; (G3:G26=E1) * (I3:I26=F1); 0)) |
Source: ChatGPT
_______________________________________
|
|
| pus acum 9 zile |
|