Hoping someone here can help with a small issue. I'm writing an array formula, which uses a vlookup to pull prices. Identical items can be either on the sales floor, or in various overstock locations, and since they gradually increase in value, only the price attached to the version thats on the sales floor should be valid. Vlookup unfortunately pulls the price for the first match it finds. I've cannibalized the following code from various sources online, and it does work at pulling the correct prices, however, it appends "Sales floor" to every price. Is there something I'm missing, or a way to further refine it?

={"Variant Price";if(Checkbox!B1=TRUE,Arrayformula(IFERROR(VLOOKUP(A2:A&"Sales Floor",Inventory!A2:K&Inventory!N2:N,Column(J2:J),false))),"")}

The variant price and checkbox portions are simply to add a title, and to be able to control whether the formula is activated, this sheet has a large number of formulas that don't need to run constantly.

CodePudding user response：

try:

={"Variant Price"; IF(Checkbox!B1=TRUE, 
 ARRAYFORMULA(SUBSTITUTE(IFERROR(VLOOKUP(A2:A&"Sales Floor", 
 Inventory!A2:K&Inventory!N2:N,Column(J2:J), 0)), "Sales Floor", )))}