Good day, in the lesson part of module 3.54, there is a note that one could save an array constant as a Named Range and refer to that in formulas. I consider such a feature extremely useful and immediately jumped in to test the waters and named column 2 of the array as Model, column 3 as Supplier and column 4 as Price.
My formula hence became: =VLOOKUP($B47,$B$37:$E$41,{"Model","Supplier","Price"},FALSE) but it gives a #REF error.
Kindly guide me to the promise land 🙂
Hi Peter,
The third argument of VLOOKUP should be a number i.e. the column number in the range B37:E41.
Your formula should translate to this:
=VLOOKUP($B47,$B$37:$E$41,{2,3,4}, FALSE)
Your formula translates to this and whatever is in the cells of those defined names:
=VLOOKUP($B47,$B$37:$E$41,{C37:C41,D37:D41,E37:E41},FALSE)
VLOOKUP can't take 2-D arrays in the lookup column argument.
If you defined a name return_cols like so:
={2,3,4}
You could then write your VLOOKUP like this:
=VLOOKUP($B47, $B$37:$E$41, return_cols, FALSE)
Although, for some reason VLOOKUP doesn't respect the array when it's in a name and only returns the first column number in return_cols, whereas this works correctly even though they translate to the same arguments:
=VLOOKUP($B47,$B$37:$E$41,{2,3,4}, FALSE)
If you used XLOOKUP, it works correctly - see file attached.
Also note, by referencing defined names in double quotes, you're referring to text, not a defined name. Not that it would work here anyway because each of your defined names return an array of values, not a single number.
Mynda
Thanks Mynda, Unfortunately, I could not open the file you have attached - it gives a "Not found Error 404"
The vlookup formula seems to work when the table_array is a named range and when the col_Index_num is a named range - Example: VLOOKUP(B47,Parts,Col_num) where Parts = table_array and Col_num = Col_Index_num and refers to the named range containing 2,3,4
The Xlookup formula seems to work well when the lookup_array and the return_array are part of a defined table. I named the sample array as Parts_Table and the formula XLOOKUP(B48,Parts_Table
,Parts_Table[[Model '#]:[Price]]) worked well - although not using named ranges as such.
XLOOKUP will work with both table names and defined names. I suspect there was something wrong with the name if it didn't work.
It's probably best if you upload your file to show what you're trying, and I can troubleshoot, rather than me trying to guess what your examples are and recreating them.
Mynda
Thanks Mynda, I have cleared the uncertainties.
