joining SUM+VLOOKUP?

[ezekiel]

Hi,
I need help to not rely on helper column. As I have big data (there are aprox. 100.000 rows every month, 6 columns, and 8 helper columns), so these helper columns was little bit slow down my file. So I just want to use helper columns as minimum as possible.

Well, in this case, every amount in column A has specific range (column K), and every range has specific margin (column L). I want to know how many is the total margin. My best approach, I can get the margin (column C) without the range column (column B) with this formula (I put it on column E, picture below) :

=VLOOKUP(VLOOKUP(A:A;E:F;2);F:G;2;0)

But I don't know how to sum all that margins. If I put SUM on formula above, it has no effect (the results are same as column C)



Is there a way to get this total margins without those helper columns? Also, I'm kinda in transition to move from Excel to Google sheet. If it's easier in Gsheet, I'll be more happy. Thanks in advance.

 

[shamimarita]

Hello Ezekiel,

I understand your concern about minimizing helper columns, especially when dealing with large datasets. To calculate the total margin without relying on helper columns, you can use the SUMPRODUCT function combined with VLOOKUP. Here's how you can do it:

• Column A contains the amounts.
• Columns E:F contain the mapping from amounts to ranges.
• Columns F:G contain the mapping from ranges to margins.

You can use the following formula to calculate the total margin:
=SUMPRODUCT(VLOOKUP(VLOOKUP(A2:A100000, E:F, 2, FALSE), F:G, 2, FALSE))

This formula performs a nested VLOOKUP to find the margin corresponding to each amount and then sums them all using SUMPRODUCT.

Google Sheets Alternative
If you're transitioning to Google Sheets, you can use the ARRAYFORMULA function to achieve the same result:

=SUM(ARRAYFORMULA(VLOOKUP(VLOOKUP(A2:A, E:F, 2, FALSE), F:G, 2, FALSE)))
This formula works similarly by performing nested VLOOKUP operations within an ARRAYFORMULA to handle the entire range and then summing the results.

Both approaches eliminate the need for helper columns and should improve the performance of your spreadsheet. Let me know if you need further assistance!