• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup example with explanation in which using range lookup as -1

R

Ramesh Deo

Member
Dear All Ninjas,

Need example with explanation of vlookup in which using range lookup -1.

Awaiting for response !

Regards
 
Somendra Misra said:
Hi Ramesh,

VLOOKUP don't have a value of (-1) as range lookup. It only has two value TRUE(1) or FALSE(0).

Regards,
Click to expand...

Hi Sm sir,
how r you? thanks for response.
but i heard by some1 ,in function vlookup we can use -1 as range lookup, i tried a lot searching such example on google but not found. so can i make sure myself that we can't use -1 in vlookup.
 
Nopes, Column or row always start with a number 1 in any table. It can be never negative.

See this from Excel Help:

If the col_index_num argument is:

  • Less than 1, VLOOKUP returns the #VALUE! error value.
  • Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
  • range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
    • If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

  • If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Regards,
 
Somendra Misra said:
Nopes, Column or row always start with a number 1 in any table. It can be never negative.

See this from Excel Help:

If the col_index_num argument is:

  • Less than 1, VLOOKUP returns the #VALUE! error value.
  • Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
  • range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
    • If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

  • If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Regards,
Click to expand...


thanks a lot sm sr for this great explanation for vlookup.
 
You must log in or register to reply here.
Back
Top