Value Look-up Formula

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I want to look up a value in a table based upon 2
criteria! The first criteria will be an income value to
be compared against a range of values in the income list.

The second criteria is a column index number.
 
One approach is to use VLOOKUP, with the 4th arg set to
TRUE and the table_array* sorted in ascending order
*this would be your "income list"

Syntax from Excel Help:
VLOOKUP
(lookup_value,table_array,col_index_num,range_lookup)

Perhaps an example to illustrate ?:

In Sheet1
----------
Set-up the "income list" in A2:B6

0 F
50 D
70 C
80 B
90 A

The above list presumes that the income is categorised
into say, classes A,B,C,D,E,F by the criteria:

A: >=90
B: >=80 to <90
C: >=70 to <80
D: >=50 to <70
F: <50

In Sheet2
----------
Assume col A contains all the income values to be classed,
in cell A2 downwards

Put in B2: =VLOOKUP(A2,Sheet1!$A$2:$B$6,2,TRUE)
Copy B2 down as many rows as there are data in col A

Col B will return the income class (A,BC,D,E,F) according
to the criteria specified
 
Back
Top