excel learner
asked on
Single Index and match formulae for multiple ranges
Dear experts,
I want a excel formulae in cell c3 in this sheet, which will match the currency in cell b2 in this sheet with the currencies mentioned in column range A in sheet data elements
Then the perform the index and match function as is in cell c3
For instance the current value showsvalue if the currency is USD(cell I9 in sheet data elements), if the currency is GBP it will be 2.56 as in cell I19 in sheet data elements
if the currency is EUR it will be 3.2 as in cell I29 in sheet data elements
Thankyou,
index-and-match-formulae.xls
I want a excel formulae in cell c3 in this sheet, which will match the currency in cell b2 in this sheet with the currencies mentioned in column range A in sheet data elements
Then the perform the index and match function as is in cell c3
For instance the current value showsvalue if the currency is USD(cell I9 in sheet data elements), if the currency is GBP it will be 2.56 as in cell I19 in sheet data elements
if the currency is EUR it will be 3.2 as in cell I29 in sheet data elements
Thankyou,
index-and-match-formulae.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I was looking at a slightly different approach, too.
This version should give the same results but would also allow you to vary the header values (although it assumes that the all the tables will still be the same size)
=LOOKUP(9.9E+307,IF({1,0}, 0,VLOOKUP( E3,OFFSET( 'data elements'!A4:K9,MATCH(B2,' data elements'!A2:A22,0)-1,0),M ATCH(C4,OF FSET('data elements'!B3:K3,MATCH(B2,' data elements'!A2:A22,0)-1,0),0 )+1,0)))
regards, barry
This version should give the same results but would also allow you to vary the header values (although it assumes that the all the tables will still be the same size)
=LOOKUP(9.9E+307,IF({1,0},
regards, barry
ASKER