Attached is the spreadsheet I am working with. I can't seem to get my index and match functions to work - probably becuase I have never used them before. Here is what I am trying to do -
I want Sheet 1 Column L to pull the population from Sheet 2 Column C if Sheet 1 Columns A and B = Sheet 2 Columns A and B. If they don't make, then an error is fine.
Can geniuses figure this out?Excel Function Help.xlsxExcel Function Help.xlsx
Assuming there are no duplicates on Sheet2..I.E.
AK and ANCHORAGE won't appear on the same row twice on Sheet2...
=SUMPRODUCT(--(Sheet2!$A$2:$A$46=A2),--(Sheet2!$B$2:$B$46=B2),Sheet2!$C$2:$C$46)
Try this...
=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,B2)
Copy down as needed.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
@Jonmo1 - maybe I misunderstood your formula, but I couldn't get it to work.
@Tony Valko - that seems to work! Thanks!
Is there a reason an index and match wouldn't work?
You're welcome!Originally Posted by djbcktt
It'll work but the SUMIFS formula is easier to write.Is there a reason an index and match wouldn't work?
Here's the equivalent INDEX/MATCH formula.
Array entered**:
=INDEX(Sheet2!C$2:C$46,MATCH(A2,IF(Sheet2!B$2:B$46=B2,Sheet2!A$2:A$46),0))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down as needed.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks