Vlookup not able to find reference cell

Question

I am using Google Sheets to look up a formula, but the code comes back saying

"unable to match'.25oz'"

The link for the sheet is here:
https://docs.google.com/spreadsheets/d/1K1Hb9xV8Ky51Vdiv0SG-zKOEH0ELPNBYwMWhDFfm4l0/edit#gid=168328825

The specific part I am having an error with is 'Master Cocktail' D5 where it tries to search either my Spirits sheet or the 'Beer NA Bev' sheet. for the appropriate price based on the oz used.

The code I am using in the cell is:

=ArrayFormula(IF(C5="","",vlookup($B5,{'Beer NA Bev'!$A$63:$H$71;Spirits!$A$2:$I$325},MATCH($C5,{'Beer NA Bev'!$A$62:$K$62;Spirits!$A$3:$J$3},0),FALSE)))

Answer 1

• first of all {'Beer NA Bev'!$A$63:$H$71; Spirits!$A$2:$I$325}
  needs to be: {'Beer NA Bev'!$A$63:$I$71; Spirits!$A$2:$I$325} eg. same amount of columns

• then you can drop the whole MATCH and put there IFERROR under which there will be IF with two VLOOKUP (one for TRUE side and one for FALSE side) to distinguish between beer and spirits because you have .25oz in both sheets. and then you can insert simple IFS to check/search for the right .25oz price and return column number for the main VLOOKUP.
  check this similar issue: https://stackoverflow.com/a/54680420/5632629

something like this:

 IFERROR(IF(VLOOKUP(B5, 'Beer NA Bev'!$A$63:$I$71, 1, 0)<>"", IFS(C5=".25oz", 5, 
                                                                  C5=".5oz",  6, 
                                                                  C5=".75oz", 7, 
                                                                  C5="1oz",   8,
                                                                  C5="2oz",   9), ), 
         IF(VLOOKUP(B5, Spirits!$A$2:$I$325,       1, 0)<>"", IFS(C5="2oz",   4,
                                                                  C5="1.5oz", 5,
                                                                  C5="1oz",   6,
                                                                  C5=".75oz", 7,
                                                                  C5=".5oz",  8,
                                                                  C5=".25oz", 9), ))

and the whole formula would be:

=ARRAYFORMULA(IF(C5="", "",
 VLOOKUP($B5, {'Beer NA Bev'!$A$63:$I$71;
                     Spirits!$A$2:$I$325},
 IFERROR(IF(VLOOKUP(B5, 'Beer NA Bev'!$A$63:$I$71, 1, 0)<>"", IFS(C5=".25oz", 5, 
                                                                  C5=".5oz",  6, 
                                                                  C5=".75oz", 7, 
                                                                  C5="1oz",   8,
                                                                  C5="2oz",   9), ), 
         IF(VLOOKUP(B5, Spirits!$A$2:$I$325,       1, 0)<>"", IFS(C5="2oz",   4,
                                                                  C5="1.5oz", 5,
                                                                  C5="1oz",   6,
                                                                  C5=".75oz", 7,
                                                                  C5=".5oz",  8,
                                                                  C5=".25oz", 9), )), 0)))