Solved

Single Index and match formulae for multiple ranges

Posted on 2011-02-13
3
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

630 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