Solved

Help with excel cell formula

Posted on 2014-01-16
1
322 Views
Last Modified: 2014-01-17
I have an existing formula

=IFERROR(INDEX(mapping!C:C,MATCH('all deals'!C2,mapping!A:A,0)),"")

It looks at my existing tab column C and the mapping tab column A and pulls in mapping column C when there is a match

I need the formula to also make sure that existing tab column J equals mapping column B

so for it to pull across mapping column C
- mapping column A must equal existing tab column C
and
- mapping column B must equal existing tab column J
0
Comment
Question by:Matt Pinkston
1 Comment
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39786255
Try:

=IFERROR(INDEX(mapping!$C$1:$C$1000,MATCH(1,INDEX((mapping!$A$1:$A$1000='all deals'!C2)*(mapping!$B$1:$B$1000='all deals'!J2),0),0)),"")

Notice that I didn't use whole columns, as this type of formula is less efficient than your original.

If th column C values are numeric, then you can use SUMIFS, eg.

=SUMIFS(mapping!C:C,mapping!A:A,'all deals'!C2,mapping!B1:B1000,'all deals'!J2)

or you can concatenate columns A and B on your mapping sheet, then reference them in a regular INDEX/Match formula with C2&J2 concatenated as lookup values....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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…
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…

757 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

17 Experts available now in Live!

Get 1:1 Help Now