I have my query which ALWAYS will bring in two rows of data, However, it may bring in only one row as well sometimes. I have my data as follows in the spreadsheet
Product Cost Units
A $10 10
B $5 5
Cell Total: SUM(10:5) (which results in 15 in this case)
Cell Percentage for Product B: =(5/15) (which results in 0.33)
Now, my issue is that when at times it only fetches ONE row of data, my product B moves up to the line where Product A is suppose to be, because there is no data for product A. How can i make my division happen on Product B even when there is only ONE row OR if there is two rows? The division ONLY needs to happen on Product B unit value.
I hope i explained it better. Can someone help out? Thank you.
Note, you should avoid using HLOOKUP and VLOOKUP. They are resource hogs that will slow your dashboard down. For a mere two rows, you won’t notice. But it’s better to avoid the habit.
The appropriate alternative is INDEX paired with MATCH. So, in your case, the formula would look like:
=SUM(B2:B3)/INDEX(B2:B3,MATCH("B",A2:A3,0))
That will give you the value in the B column when the A column is “B”.
(Note, always use “0 - Exact match” as the third option in the MATCH function. Unless you really want the closest available match and have your results sorted appropriately, which is rarely the case.)
