Combine and Sum Lookups across multiple rows using an array formula

pkh

New Member
Joined
Nov 23, 2011
Messages
21
Hi, I need to convert several currencies across multiple rows into GBP by looking up a conversion table and then provide the sum in GBP. I haven't been able to come up with an elegant formula (an array type formula maybe) to keep it short and simple.
1590645118088.png


for the SUM in GBP, I've got the following formula:
SUM(C8/INDEX($C$2:$C$4,MATCH(B8,$B$2:$B$4,0),),C9/INDEX($C$2:$C$4,MATCH(B9,$B$2:$B$4,0),),C10/INDEX($C$2:$C$4,MATCH(B10,$B$2:$B$4,0),))

But this is not practical as the actual table of items has a lot more rows. Any help would be much appreciated.

Regards,
pkh
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,538
Office Version
2019
Platform
Windows
See if this works

=SUMPRODUCT($C$8:$C$10/INDEX($C$2:$C$4,MATCH(T(IF({1},$B$8:$B$10)),$B$2:$B$4,0)))
 

pkh

New Member
Joined
Nov 23, 2011
Messages
21
See if this works

=SUMPRODUCT($C$8:$C$10/INDEX($C$2:$C$4,MATCH(T(IF({1},$B$8:$B$10)),$B$2:$B$4,0)))
Jasonb75,

Exactly what I was looking for!!! Worked like a dream :). Thank you very much.

regards,
pk
 

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
54
Office Version
365
Platform
Windows
Try below formula:

=SUMPRODUCT(SUMIFS(C8:C10,B8:B10,B2:B4)/C2:C4)


Regards,
Amit Tandon
 
Last edited by a moderator:

pkh

New Member
Joined
Nov 23, 2011
Messages
21
Amit,

Thanks for this. Also very elegant. Much appreciated.

Regards,
pkh
A
Try below formula:

=SUMPRODUCT(SUMIFS(C8:C10,B8:B10,B2:B4)/C2:C4)


Regards,
Amit Tandon
www.excelanytime.com
Amit,

Actually this worked even better because i could extend the formula to a blank row below without getting #NA errors, which I could not avoid in the index match approach without specifying a blank row in the lookup array. I don't like using ISNA as it can hide other errors.

Thanks,
pkh
 

Watch MrExcel Video

Forum statistics

Threads
1,099,006
Messages
5,465,965
Members
406,457
Latest member
Pinky Rose Jordan

This Week's Hot Topics

Top