VLOOKUP Formula - improvement possible?

[tlc53]

Hi,

This formula works, but I was just wondering if there was a more condensed/better way to write it..

=VLOOKUP("716",BJTABLE,10,FALSE)+VLOOKUP("717",BJTABLE,10,FALSE)+VLOOKUP("718",BJTABLE,10,FALSE)+VLOOKUP("719",BJTABLE,10,FALSE)

Thanks

 

[Burrgogi]

That would depend on how your spreadsheet is set up and the specific version of MS Excel you're using. Why don't you fill out your profile & tell us the version of MS Excel you have?

 

[tlc53]

That would depend on how your spreadsheet is set up and the specific version of MS Excel you're using. Why don't you fill out your profile & tell us the version of MS Excel you have?

Done!

 

[Alex Blakenburg]

Is BJTABLE a named range or an actual Table ?
Does each number only appear once ?
In MS365 (thanks @Burrgogi) you have multiple options here is one:
Put the column references where I have B and A

=SUM(SUMIFS($B$1:$B$10,$A$1:$A$10,{716,717,718,719}))

 

[Alex Blakenburg]

The previous option will sum the values if there are multiple lines meeting the criteria.
I will also pick up both numeric and text values matching the values in the {}

A couple of other options that will only pick up the first match and using the quote marks as you have done will only pick up text values:

=SUM(IFERROR(VLOOKUP({"716","717","718","719"},BJTABLE,10,FALSE),0))

=SUM(XLOOKUP({"716","717","718","719"},BJTABLE[ID],BJTABLE[Amt],0))

 

[tlc53]

The previous option will sum the values if there are multiple lines meeting the criteria.
I will also pick up both numeric and text values matching the values in the {}

A couple of other options that will only pick up the first match and using the quote marks as you have done will only pick up text values:

=SUM(IFERROR(VLOOKUP({"716","717","718","719"},BJTABLE,10,FALSE),0))

=SUM(XLOOKUP({"716","717","718","719"},BJTABLE[ID],BJTABLE[Amt],0))

I tried the first formula and it worked perfectly. Thank you!
That was just what I wanted. Looks much tidier.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.