Solved

Index w/ Match variables

Posted on 2013-11-18
2
169 Views
Last Modified: 2014-04-22
Hello,

The formula I'm using is:

=index(WR!A:G,match(A1&A2,WR!A:A&WR!F:F,0),2)

This works fine so long as the values in A1 and A2 are fixed values.

But what I'd like to do is replace A2 something that says "anything greater than zero." Fiddling with the match_type just returns the wrong values.

Thanks in advance.

Seth
0
Comment
Question by:IntegriNet
  • 2
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39657756
Try changing it around like so:

=INDEX(WR!A:G,MATCH(1,INDEX((WR!A:A>0)*(WR!F:F=A2),0),0),2)

note, this formula doesn't need CTRL+SHIFT+ENTER confirmation, but it is an array formula and so you should minimize the range sizes for better efficiency.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39850021
Hi Seth,

I was wondering if you tried this solution and if it was successful?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now