Question:

Hi,

What type of formula could be used if you weren't using a date range and your data was not concatenated?

ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 and Range2

Range1 Range2 Value
1.33 1.66 A
1.67 1.99 B
2.00 2.33 C

Quickly lookup a value in a numerical range

You can also do lookups in date ranges, dates in Excel are actually numbers.

LOOKUP function

The easiest way to go is the LOOKUP function, watch a video below to see how it works:

The picture below shows you data in cell range B3:C6, the search value is in C8 and the result is in C10.

Cell range B4:B6 must be sorted in ascending order for the LOOKUP function to work properly if an exact match is not found the largest value is returned as long as it is smaller than the lookup value. The LOOKUP function then returns a value in a column on the same row.

Example, Search value 1.71 has no exact match, the largest value that is smaller than 1.71 is 1.67. The returning value is found in column C on the same row as 1.67, in this case, B.

If the search value is smaller than the smallest value in the lookup range the function returns #N/A meaning Not Available or does not exist. Example in above picture, search value is 1 in the and the LOOKUP function returns #N/A.

A search value greater than the largest value in the lookup range matches the largest value. Example in above picture, search value is 3 and the returning value is C.

The formula in cell C10:

=LOOKUP(C8,B4:B6,C4:C6)

Learn more about the LOOKUP function, recommended reading:

LOOKUP function

Finds a value in a sorted cell range and returns a value on the same row.

Comments(0) Filed in category: Excel, Lookup function

Tip! - You can quickly sort a cell range, follow these steps:

  1. Right-click on a cell in the cell range you want to sort
  2. Hover with mouse cursor over Sort
  3. Click on "Sort Smallest to Largest"

INDEX + SUMPRODUCT + ROW

The following formula is slightly larger but you don't need to sort cell range B4:B6.

The formula in cell C11:

=INDEX(D4:D6, SUMPRODUCT(--($D$8<=C4:C6), --($D$8>=B4:B6), ROW(A1:A3)))

The ranges don't need to be sorted however you need a start (Range1) and an end value (Range2).

Recommended reading

Return linear value if in range
This post explains how to return a linear value depending on lookup value and range

Explaining formula in cell C11

You can easily follow along, go to tab "Formulas" and click "Evaluate Formula" button. Click "Evaluate" button to move to next step.

Step 1 - Calculate first criterion

=INDEX(D4:D6, SUMPRODUCT(--($D$8<=C4:C6), --($D$8>=B4:B6), ROW(A1:A3)))

Step 1 - Calculate second criterion

--($D$8<=C4:C6)

becomes

--(1,78<={1,66;1,99;2,33})

becomes

--({FALSE;TRUE;TRUE})

and returns

{0;1;1}

Step 2 - Calculate second criterion

=INDEX(D4:D6, SUMPRODUCT(--($D$8<=C4:C6), --($D$8>=B4:B6), ROW(A1:A3)))

--($D$8>=B4:B6)

becomes

--(1,78>={1,33;1,67;2})

becomes

--({TRUE;TRUE;FALSE})

and returns

{1;1;0}

Step 3 - Create row numbers

=INDEX(D4:D6, SUMPRODUCT(--($D$8<=C4:C6), --($D$8>=B4:B6), ROW(A1:A3)))

ROW(A1:A3)

returns

{1;2;3}

Step 4 - Multiply criteria and row numbers and sum values

=INDEX(D4:D6, SUMPRODUCT(--($D$8<=C4:C6), --($D$8>=B4:B6), ROW(A1:A3)))

SUMPRODUCT(--($D$8<=C4:C6), --($D$8>=B4:B6), ROW(A1:A3))

becomes

SUMPRODUCT({0;1;1}, {1;1;0}, {1;2;3})

and returns number 2.

Step 5 - Return a value of the cell at the intersection of a particular row and column

=INDEX(D4:D6, SUMPRODUCT(--($D$8<=C4:C6), --($D$8>=B4:B6), ROW(A1:A3)))

becomes

=INDEX(D4:D6, 2)

becomes

=INDEX({"A";"B";"C"}, 2)

and returns "B".

Functions in this formula: INDEX, SUMPRODUCT, ROW

VLOOKUP function

=VLOOKUP($D$8,$B$4:$D$6,3,TRUE)

The VLOOKUP function requires your ranges to be sorted like the LOOKUP function above, the advantage is that you need only Range1 column.

Recommended article

Return value if in range using multiple tables
How to use multiple lookup tables and return corresponding value if in range

Explaining VLOOKUP formula in cell C10

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.

range_lookup is optional. range_lookup is either TRUE (or omitted) or FALSE . TRUE: exact match or an approximate match. If an exact match is not found, the next largest value that is less than lookup_value is returned. The table_array must be sorted in ascending order.

If range_lookup is FALSE the table_array does not need to be sorted however, then the VLOOKUP function won't work in our case, if there isn't an exact match.

=VLOOKUP($D$8,$B$4:$D$6,3,TRUE)

becomes

=VLOOKUP(1,78,{1,33, 1,66, "A";1,67, 1,99, "B";2, 2,33, "C"},3,TRUE)

1,67 is the next largest value and the VLOOKUP function returns "B".

INDEX + MATCH

Formula in cell C10:

=INDEX($D$4:$D$6,MATCH(D8,$B$4:$B$6,1))

The lookup range must be sorted, just like the LOOKUP and VLOOKUP functions. Functions in this formula: INDEX and MATCH

Thanks JP!

Recommended article

Two-way lookup in multiple cross reference tables
Lookups in multiple cross reference tables

Explaining INDEX+MATCH in cell D10

=INDEX($D$4:$D$6,MATCH(D8,$B$4:$B$6,1))

Step 1 - Return the relative position of an item in an array

MATCH(lookup_value,lookup_array, [match_type]) returns the relative position of an item in an array that matches a specified value

[match_type] is optional. The number can be -1, 0, or 1. 1 is default.  The match_type argument specifies how Excel matches lookup_value with values in lookup_array.

MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order.

=INDEX($D$4:$D$6,MATCH(D8,$B$4:$B$6,1))

MATCH(D8,$B$4:$B$6,1)

becomes

MATCH(1.78,{1.33;1.67;2},1)

1.67 is the largest value that is less than or equal to lookup_value. 1.67 is the second value in the array. MATCH function returns 2.

Step 2 - Return a value of the cell at the intersection of a particular row and column

=INDEX($D$4:$D$6,MATCH(D8,$B$4:$B$6,1))

becomes

=INDEX($D$4:$D$6,2)

becomes

=INDEX({"A";"B";"C"},2)

and returns "B".

Download excel sample file for this tutorial

Return value if in rangev3.xlsx
(Excel 2007 and later versions, Workbook *.xlsx)