Sometimes we need to look up 2 values to get our desired match. Most of times such data is presented in a matrix form.
Example: We need to get rate for Currency Category pair from the Rate matrix.
To look up such data, here I will use the offset function and match function.
Offset function format : OFFSET(reference, rows, cols, [height], [width])
Match function format: MATCH(lookup_value, lookup_array, [match_type])
Formula for the first pair (cell D7): =OFFSET($A$1,MATCH(B7,$A$2:$A$4,0),MATCH(C7,$B$1:$C$1,0),1,1)
Example: We need to get rate for Currency Category pair from the Rate matrix.
|
Rate matrix
|
||
|
|
1
|
2
|
|
INR
|
1.1
|
2
|
|
AUD
|
0.25
|
0.35
|
|
USD
|
0.15
|
0.5
|
|
TransID
|
Curr
|
Category
|
Rate
|
|
19000A2
|
AUD
|
2
|
|
|
19000A3
|
AUD
|
1
|
|
|
19000A4
|
INR
|
1
|
|
To look up such data, here I will use the offset function and match function.
Offset function format : OFFSET(reference, rows, cols, [height], [width])
Match function format: MATCH(lookup_value, lookup_array, [match_type])
Formula for the first pair (cell D7): =OFFSET($A$1,MATCH(B7,$A$2:$A$4,0),MATCH(C7,$B$1:$C$1,0),1,1)
