Solved

Single Index and match formulae for multiple ranges

Posted on 2011-02-13
3
247 Views
Last Modified: 2012-05-11
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
Comment
Question by:Excellearner
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 34882311
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
 

Author Closing Comment

by:Excellearner
ID: 34882323
Barry, excellent as usual.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34882331
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question