How To Find Non Numeric Data In Excel

Employ the LOOKUP part to await up a value in a one-column or one-row range, and think a value from the same position in another 1-column or one-row range. The lookup function has 2 forms, vector and array. The bulk of this article describes the vector form, simply the concluding example below illustrates the assortment form.

The LOOKUP function accepts three arguments:lookup_value, lookup_vector, and result_vector. The first argument, lookup_value,is the value to expect for. The second argument,lookup_vector, is a ane-row, or i-column range to search. LOOKUP assumes that lookup_vector is sorted in ascending social club. The 3rd statement,result_vector, is a i-row, or i-column range of results. Result_vector is optional. When result_vector is provided, LOOKUP locates a match in the lookup_vector, and returns the respective value from result_vector. If result_vector is not provided, LOOKUP returns the value of the lucifer found in lookup_vector.

LOOKUP has default behaviors that make it useful when solving certain problems. For instance, LOOKUP can be used to retrieve an approximate-matched value instead of a position and to find the last value in a row or column. LOOKUP assumes that values in lookup_vector are sorted in ascending guild and always performs an approximate match. When LOOKUP can't find a friction match, it will match the side by side smallest value.

Example #1 - bones usage

In the instance shown above, the formula in cell F5 returns the value of the match found in cavalcade B. Annotation that result_vector is not provided:

                                    =              LOOKUP              (F4,B5:B9)              // returns match in level

The formula in prison cell F6 returns the corresponding Tier value from column C. Detect in this example, both lookup_vector andresult_vector are provided:

                                    =              LOOKUP              (F4,B5:B9,C5:C9)              // returns respective tier

In both formulas, LOOKUP automatically performs an approximate friction match and it is therefore of import that lookup_vector is sorted in ascending society.

Example #ii - terminal non-empty jail cell

LOOKUP can be used to go the value of the last filled (non-empty) cell in a column. In the screen below, the formula in F6 is:

Get value of last non-empty cell with LOOKUP

Annotation the use of a full cavalcade reference. This is not an intuitive formula, only information technology works well. The key to agreement this formula is to recognize that the lookup_value of 2 is deliberately larger than any values that volition appear in the lookup_vector. Detailed caption here.

Example #3 - latest price

Like to the above case, the lookup function can be used to look upwards the latest price in data sorted in ascending club by appointment. In the screen below, the formula in G5 is:

where item (B5:B12) and toll (D5:D12) are named ranges.

Example of LOOKUP function to find latest price

When lookup_value is greater than all values inlookup_array, default behavior is to "fall dorsum" to the previous value. This formula exploits this behavior past creating an array that contains only 1s and errors, then deliberately looking for the value 2, which will never exist found. More details here.

Instance #4 - array class

The LOOKUP role has an array form likewise. In the assortment configuration, LOOKUP takes only two arguments: the lookup_value, and a single ii-dimensional array:

                                    LOOKUP              (lookup_value,              array)              // array grade

In the assortment form, LOOKUP evaluates the assortment and automatically changes behavior based on the array dimensions. If the assortment is wider than alpine, LOOKUP looks for the lookup value in the showtime row of the assortment (like HLOOKUP). If the array is taller than wide (or square), LOOKUP looks for the lookup value in the first column (similar VLOOKUP). In either case, LOOKUP returns a value at the same position from the last row or column in the array. The example beneath shows how the array form works. The formula in F5 is configured to use a vertical assortment and the formula in F6 is configured to utilise a horizontal array:

                                    =              LOOKUP              (E5,B5:C9)              // vertical assortment              =              LOOKUP              (E6,C11:G12)              // horizontal array

LOOKUP function array form example

The vertical and horizontal arrays contain the same values; only the orientation is unlike.

Note: Microsoft discourages the use of the array form and suggestsVLOOKUP and HLOOKUP equally ameliorate options.

Notes

LOOKUP assumes that lookup_vector is sorted in ascending club.
When lookup_value tin't be found, LOOKUP will friction match the next smallest value.
When lookup_value is greater than all values in lookup_vector, LOOKUP matches the last value.
When lookup_value is less than the first value in lookup_vector, LOOKUP returns #N/A.
Result_vector must be the aforementioned size as lookup_vector.
LOOKUP is not instance-sensitive