The formula in test!B7:

=INDEX(new!B$3:B$71,MATCH(test!$A7,new!$A$3:$A$71, 0))

INDEX has the following form: =INDEX(array, r, c)
where array is a range of cells (or an array constant), r is row number and c is column number. array can be one column or row or more columns or rows.
In the formula above array (=new!B3:B71) is one column. In that case the third argument (c) is not necessary. To determine r we use another function: MATCH(test!$A7,new!$A$3:$A$71,0). Match has syntax:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value is here test!$A7, lookup_array is your subcategory in sheet new. To understand the third argument [match_type] you should read Excel Help to that function. 0 or FALSE as third argument means that Excel finds the first value that is exactly equal to lookup_value. No need for any sorting here.

I attach a new file where I have tried to change it so that Category is in column A and Subcategory in column B in sheet new. Furthermore I have created three names (see Name Manager) category, subcategory and cost.type. This naming isn't necessary but I find it convenient.

Attached Files

test_3.xlsx (64.4 KB, 12 views)