"VLOOKUP and HLOOKUP .... functions do not always behave as you would expect and they are less flexible when you want to return a corresponding value in a non-contiguous range..." Umm, not really.

On the contrary, these two functions can be used, in conjunction with CHOOSE, the look left or up respectively in a non-contiguous range to return a corresponding value - just like the INDEX/MATCH combo does!!

See my comment on Liam's earlier post "Lookout for VLOOKUP and HLOOKUP" about the above technique, or go direct to Mynda Treacy's tutorial on her MyOnlineTrainingHub blog at https://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose, or search the web to find a several tutorials that explain how to achieve this.

VLOOKUP and HLOOKUP can still MATCH it with most other functions IF we CHOOSE to LOOKUP the web or have a PROPER SEARCH in the INDEX, otherwise we ISERROR!! It's TRUE!

With all due respect, "In fact, they can never be used to return data for columns to the left (VLOOKUP) or rows above (HLOOKUP)" is simply NOT TRUE!!

All too often VLOOKUP and HLOOKUP are accused of deficiencies, but this is usually done in unfair comparison with two other functions used in combination (e.g. INDEX & MATCH) to overcome a deficiency/limitation in the naked function.

VLOOKUP and HLOOKUP can be used to look in the opposite direction (i.e. left or up respectively) by using the CHOOSE function to return the table_array argument, and the integer 2 for the column_index_num. This works in an almost identical way to an IDNEX/MATCH combo - even allowing non-contiguous ranges to be specified for the location of the lookup and return values!

What evidence do you have for the following sweeping statement?
"And immediately behind every good FD there is usually an exceptional (often) female financial controller ..."

I was just pointing out the unfairness of the single function vs combo comparison and that several of the arguments used to discredit Vlookup are flawed. (I've seen many Excel bloggers do this, and can't understand why!)

And I wasn't making a judgement as to which of VLOOKUP or INDEX/MATCH was easier/better in various circumstances, though I do think there's little difference between using INDEX/MATCH and VLOOKUP/CHOOSE for straightforward (columnar) lookups, and agree that the former does offer more options, particularly where range intersections are required.

Why does almost every Excel blogger blindly compare Vlookup in a native/naked state (as a single function) with an Index/Match combo (at least one secondary function returning arguments to the primary function)??

This is a grossly unfair comparison, because the perceived (but not real) limitations of Vlookup can be easily overcome by nesting it with other functions!
For example:
1. Two Column functions, one Columns function, or even the Match function can dynamically return the col_index_num argument.
2. The Choose function can be used to make Vlookup to act very much like an Index/Match combo, by:
(a) looking to the left, and
(b) referencing two non-contiguous columns for the table _array consisting of a lookup value range and a return value range (vs a large contiguous range with superfluous columns between the two target columns).
This can be achieved by using the following formula syntax: =VLOOKUP( Lookup_value, CHOOSE({1,2}, Lookup_range, Return_value_range), 2, FALSE ).
Note that using {1,2} does NOT make this an array/CSE formula as CHOOSE accepts the array as an argument.
Check out the full explanation/tutorial on Excel MVP Mynda Treacy's MyOnlineTrainingHub website.

Using CHOOSE to feed VLOOKUP in this way also means that only the target cells in the two relevant columns are referenced rather than cells in all columns in between - just like an INDEX/MATCH combo!

Note that the curly brackets in the first argument of the CHOOSE function {1,2} does NOT make this formula an array (CSE) formula, as CHOOSE can accept arrays.

The column_index_num is hard-coded as"2" because there are only ever 2 columns, being the Lookup_range and the Return_value_range.

The last argument for the VLOOKUP function (i.e. [range_lookup]) can be either True or False depending on your need.

The oft-quoted deficiencies of VLOOKUP are grossly overstated - mostly done when comparing a naked VLOOKUP (i.e. no other functions nested within it) with combination formulas such as INDEX/MATCH or INDEX/MATCH/MATCH. As demonstrated above, other Excel functions can be used to return the arguments of VLOOKUP, including CHOOSE, MATCH, COLUMN, and COLUMNS. All that is required is a good understanding of how Excel's functions work and what result they return - and a bit of lateral thinking.

I'd be astounded if the following comments had any sound basis:
“I coach a lot of accountants who are moving in the job market on a one-to-one basis, and I know the profession – many of them are not experienced in a customer-facing environment and haven’t had experience speaking to the media, so it can be difficult to make that transition [speaking on camera],” said Gray.

“And if the candidate has not had experience of speaking on camera, they might not give a good representation of themselves despite being a really good candidate, meaning that the organisation could lose out,” he added.

Job interviewing has got nothing whatsoever to do with being "... experienced in a customer-facing environment". If an accountant has trouble facing a camera on their laptop for a job interview, how on earth are they going to handle a face-to-face interview?

I'm dismayed, but not surprised, by your comment "Rarely do more than 50% of the audience say yes to my standard question: 'Are you fully competent in the use of absolute and relative cell references that is the use of dollar signs in cell references?'”

I think the primary cause of this problem are training organizations (the world over!) that don't cover cell referencing in their Excel Basics/ Fundamentals courses - instead leaving this core concept until their Intermediate or Advanced courses! Go figure? How can they explain the grid and formulas (the very DNA of spreadsheets) without also covering cell referencing?

1. "Range Names don't have to refer to a cell or a range of cells. They can refer to a formula." are interesting statements, for it was once explained to me that Defined Names are nothing more than formulas with a name assigned as a substitute to cell references! Names that refer to a cell range are just formula referring to an array (which could even be a range in a workbook that is different to where the Name itself is hosted).

2. Names can also be used to hold constants, such as a VAT or income tax rate (e.g. "Tax" RefersTo =20%), text strings (e.g. "Coy" RefersTo = "My Company LLB", or a multiple used for rounding values (e.g. "Million" RefersTo = 10^6)

## My answers

"VLOOKUP and HLOOKUP .... functions do not always behave as you would expect and they are less flexible when you want to return a corresponding value in a non-contiguous range..." Umm, not really.

On the contrary, these two functions can be used, in conjunction with CHOOSE, the look left or up respectively in a non-contiguous range to return a corresponding value - just like the INDEX/MATCH combo does!!

See my comment on Liam's earlier post "Lookout for VLOOKUP and HLOOKUP" about the above technique, or go direct to Mynda Treacy's tutorial on her MyOnlineTrainingHub blog at https://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose, or search the web to find a several tutorials that explain how to achieve this.

VLOOKUP and HLOOKUP can still MATCH it with most other functions IF we CHOOSE to LOOKUP the web or have a PROPER SEARCH in the INDEX, otherwise we ISERROR!! It's TRUE!

With all due respect, "In fact, they can never be used to return data for columns to the left (VLOOKUP) or rows above (HLOOKUP)" is simply NOT TRUE!!

All too often VLOOKUP and HLOOKUP are accused of deficiencies, but this is usually done in unfair comparison with two other functions used in combination (e.g. INDEX & MATCH) to overcome a deficiency/limitation in the naked function.

VLOOKUP and HLOOKUP can be used to look in the opposite direction (i.e. left or up respectively) by using the CHOOSE function to return the table_array argument, and the integer 2 for the column_index_num. This works in an almost identical way to an IDNEX/MATCH combo - even allowing non-contiguous ranges to be specified for the location of the lookup and return values!

Check out the tutorial on Mynda Treacy's MyOnlineTrainingHub blog: https://www.myonlinetraininghub.com/excel-vlookup-to-the-left-using-choose

or search the web where you'll find a number of variations on the same theme.

VLOOKUP and HLOOKUP are NOT as impotent as many espouse!

Exactly! Unfortunately, math's and logic are not strengths of the gender equality zealots, so specious arguments abound.

What evidence do you have for the following sweeping statement?

"And immediately behind every good FD there is usually an exceptional (often) female financial controller ..."

I was just pointing out the unfairness of the single function vs combo comparison and that several of the arguments used to discredit Vlookup are flawed. (I've seen many Excel bloggers do this, and can't understand why!)

And I wasn't making a judgement as to which of VLOOKUP or INDEX/MATCH was easier/better in various circumstances, though I do think there's little difference between using INDEX/MATCH and VLOOKUP/CHOOSE for straightforward (columnar) lookups, and agree that the former does offer more options, particularly where range intersections are required.

Why does almost every Excel blogger blindly compare Vlookup in a native/naked state (as a single function) with an Index/Match combo (at least one secondary function returning arguments to the primary function)??

This is a grossly unfair comparison, because the perceived (but not real) limitations of Vlookup can be easily overcome by nesting it with other functions!

For example:

1. Two Column functions, one Columns function, or even the Match function can dynamically return the col_index_num argument.

2. The Choose function can be used to make Vlookup to act very much like an Index/Match combo, by:

(a) looking to the left, and

(b) referencing two non-contiguous columns for the table _array consisting of a lookup value range and a return value range (vs a large contiguous range with superfluous columns between the two target columns).

This can be achieved by using the following formula syntax: =VLOOKUP( Lookup_value, CHOOSE({1,2}, Lookup_range, Return_value_range), 2, FALSE ).

Note that using {1,2} does NOT make this an array/CSE formula as CHOOSE accepts the array as an argument.

Check out the full explanation/tutorial on Excel MVP Mynda Treacy's MyOnlineTrainingHub website.

Despite what the vast majority Excel users learn, a VLOOKUP function can actually be directed to look to the left. Yes, really!! :-)

A formula using the following syntax will perform a right to left lookup - even on non-adjacent columns!

=VLOOKUP( Lookup_value, CHOOSE({1,2}, Lookup_range, Return_value_range), 2, FALSE )

Using CHOOSE to feed VLOOKUP in this way also means that only the target cells in the two relevant columns are referenced rather than cells in all columns in between - just like an INDEX/MATCH combo!

Note that the curly brackets in the first argument of the CHOOSE function {1,2} does NOT make this formula an array (CSE) formula, as CHOOSE can accept arrays.

The column_index_num is hard-coded as"2" because there are only ever 2 columns, being the Lookup_range and the Return_value_range.

The last argument for the VLOOKUP function (i.e. [range_lookup]) can be either True or False depending on your need.

The oft-quoted deficiencies of VLOOKUP are grossly overstated - mostly done when comparing a naked VLOOKUP (i.e. no other functions nested within it) with combination formulas such as INDEX/MATCH or INDEX/MATCH/MATCH. As demonstrated above, other Excel functions can be used to return the arguments of VLOOKUP, including CHOOSE, MATCH, COLUMN, and COLUMNS. All that is required is a good understanding of how Excel's functions work and what result they return - and a bit of lateral thinking.

I'd be astounded if the following comments had any sound basis:

“I coach a lot of accountants who are moving in the job market on a one-to-one basis, and I know the profession – many of them are not experienced in a customer-facing environment and haven’t had experience speaking to the media, so it can be difficult to make that transition [speaking on camera],” said Gray.

“And if the candidate has not had experience of speaking on camera, they might not give a good representation of themselves despite being a really good candidate, meaning that the organisation could lose out,” he added.

Job interviewing has got nothing whatsoever to do with being "... experienced in a customer-facing environment". If an accountant has trouble facing a camera on their laptop for a job interview, how on earth are they going to handle a face-to-face interview?

I'm dismayed, but not surprised, by your comment "Rarely do more than 50% of the audience say yes to my standard question: 'Are you fully competent in the use of absolute and relative cell references that is the use of dollar signs in cell references?'”

I think the primary cause of this problem are training organizations (the world over!) that don't cover cell referencing in their Excel Basics/ Fundamentals courses - instead leaving this core concept until their Intermediate or Advanced courses! Go figure? How can they explain the grid and formulas (the very DNA of spreadsheets) without also covering cell referencing?

1. "Range Names don't have to refer to a cell or a range of cells. They can refer to a formula." are interesting statements, for it was once explained to me that Defined Names are nothing more than formulas with a name assigned as a substitute to cell references! Names that refer to a cell range are just formula referring to an array (which could even be a range in a workbook that is different to where the Name itself is hosted).

2. Names can also be used to hold constants, such as a VAT or income tax rate (e.g. "Tax" RefersTo =20%), text strings (e.g. "Coy" RefersTo = "My Company LLB", or a multiple used for rounding values (e.g. "Million" RefersTo = 10^6)