This thing is out of my league (SUM+lOOKUPV+INDIRECT in the same formula)

[_Gandour_]

Hello everyone,

Im going crazy trying to make an excel sheet that is kinda out of my league.

I have different worksheets with data arranged as follows, beggining at B5

Worksheet 1

Milk....2
Eggs...3
Yest...1

Worksheet 2

Milk....2
Eggs...3
Yest...1
Lettuce...4

My objective was to create another table that would sum the different ingredients, so i made a column with all the possible ingredients next to this crazy formula that would refer to the sheet name in a dropdown list with INDIRECT.

=SUM(LOOKUPV(D24;INDIRCT("'"&$E$5&"'!"&"B5:C26");2;0);LOOKUPV(D24;INDIRECT("'"&$E$6&"'!"&"B5:C26");2;0)


Ok, it KINDA works. Problem is, if one of the ingredients is not on both worksheets (see: Lettuce) its going to give me error instead of 4. Is there a way to fix that?

 

[lrobbo314]

There is a way to do it using PowerQuery. It's not exactly difficult, but there are a fair few steps that aren't exactly intuitive if you've never worked with PowerQuery before.

Here are a couple of links that might help you get going with it.

https://community.powerbi.com/t5/Desktop/MATCH-between-2-tables/td-p/87521
https://exceleratorbi.com.au/dmv-extract-measures-power-pivot/

 

[mopp1]

Hello everyone,

Im going crazy trying to make an excel sheet that is kinda out of my league.

I have different worksheets with data arranged as follows, beggining at B5

Worksheet 1

Milk....2
Eggs...3
Yest...1

Worksheet 2

Milk....2
Eggs...3
Yest...1
Lettuce...4

My objective was to create another table that would sum the different ingredients, so i made a column with all the possible ingredients next to this crazy formula that would refer to the sheet name in a dropdown list with INDIRECT.

=SUM(LOOKUPV(D24;INDIRCT("'"&$E$5&"'!"&"B5:C26");2;0);LOOKUPV(D24;INDIRECT("'"&$E$6&"'!"&"B5:C26");2;0)


Ok, it KINDA works. Problem is, if one of the ingredients is not on both worksheets (see: Lettuce) its going to give me error instead of 4. Is there a way to fix that?

Wrap your lookups in iferror return 0. I.e, IFERROR(LOOKUPV(D24....),0)

 

[_Gandour_]

Wrap your lookups in iferror return 0. I.e, IFERROR(LOOKUPV(D24....),0)

Wow, i didnt know IFERROR existed. Thank you, worked great.