[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Help with excel cell formula

Posted on 2014-01-16
Medium Priority
340 Views
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
Question by:Matt Pinkston
[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
1 Comment

LVL 23

Accepted Solution

NBVC earned 2000 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

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month13 days, 12 hours left to enroll