Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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
0
Excellearner
Asked:
Excellearner
  • 2
1 Solution
 
barry houdiniCommented:
Hello Excellearner,

Given that the row labels and column headers are identical in each table you could just use an OFFSET function in place of the INDEX range, i.e.

=IF(COUNT(MATCH(C4,'data elements'!B3:K3,0)*MATCH(E3,'data elements'!A4:A9,0)),INDEX(OFFSET('data elements'!B4:K9,MATCH(B2,'data elements'!A2:A22,0)-1,0),MATCH(E3,'data elements'!A4:A9,0),MATCH(C4,'data elements'!B3:K3,0)),0)

If you want the headers to be different for any table then it would get more complicated.....

regards, barry
0
 
ExcellearnerAuthor Commented:
Barry, excellent as usual.
0
 
barry houdiniCommented:
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),MATCH(C4,OFFSET('data elements'!B3:K3,MATCH(B2,'data elements'!A2:A22,0)-1,0),0)+1,0)))

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now