 |
| Searching for Values in 2D Tables |
 |
Ok, so let's say that you have the following table:
|
A |
B |
C |
D |
E |
F |
G |
| 1 |
|
a1 |
a2 |
b |
c1 |
c2 |
c3 |
| 2 | X1 | 1 | 2 | 3 | 4 | 5 | 6 |
| 3 | X2 | 7 | 8 | 9 | 10 | 11 | 12 |
| 4 | Y1 | 13 | 14 | 15 | 165 | 17 | 18 |
| 5 | Y2 | 19 | 20 | 21 | 22 | 23 | 24 |
and you want to search for values given a row id (here - X1 to Y2) and a column id (here - a1 to c3).
Two functions will help us here:
- MATCH - returns the position of a value in an array or list,
- INDEX - returns a value from a table, based on an index number.
MATCH will be used to find the row and column,
then INDEX to find the value on the intersection of these two.
So, let's say that we're searching for the value in column b and row X2:
- MATCH("b", B1:G1, 0) returns 3 (the third column of the table);
the third parameter - 0 - means that we're looking for exact matches only (for "b"),
- MATCH("X2", A2:A5, 0) returns 2 (the second row of the table),
Now, by using these two values in the INDEX function, we can get the sought value:
INDEX(B2:G5, MATCH("X2", A2:A5, 0), MATCH("b", B1:G1, 0)) returns... 9!
(Note that in Excel you pass the row as the first parameter, then the column.)
HTH
Top
|
| Comments |
#1
Mario
wrote on
2009-02-25 10:37:23
|
Czesc :) widze ze przerzuciles sie na Excela :)
to wale poprawke (trzeba w index jeszcze podac zakres tabeli zeby bylo dobrze :)):
INDEX(B2:G5, MATCH("X2", A2:A5, 0), MATCH("b", B1:G1, 0))
Pozdro,
Mario
|
#2
trybik
wrote on
2009-02-25 11:02:36
|
Dzięki!
Poprawione też w tek¶cie.
|
|
Top |
|
Top
|
Texts
