VLOOKUP using COLUMNS Function Explained
In our VLOOKUP equation above (in cell C16), our COLUMNS recipe is COLUMNS($B4:B4)+1, because,
- The segment we need returned is the second section in our VLOOKUP table,
- Whatâs more, since =COLUMNS($B4:B4) assesses to 1 we really want to add 1 to get 2 for the subsequent segment.
In the VLOOKUP function in cell D16 our COLUMNS capability is COLUMNS($B4:C4)+1 and it assesses to 2+1=3. Weâve utilized an outright cell reference on $B4:C4 (=2) so that when we replicated the VLOOKUP recipe opposite C16 to D16, the COLUMNS a piece of the equation will naturally increment by 1 (from $B4:B4 to $B4:C4) to give us the right segment number.
Excel VLOOKUP with Dynamic Column Reference
Suppose anytime you work with colossal tables of data and you want to implant a VLOOKUP function that dynamically updates to the accompanying portion as you copy it across. In that case, the VLOOKUP with the COLUMNS capacity is what you truly care about. That is; the col_index_num a piece of the VLOOKUP capacity intensely revives as you copy it across your worksheet. Before learning VLOOKUP with Dynamic Column Reference, letâs first learn what is VLOOKUP function,
Contact Us