Can I substitute Vlookup with Index

[paxon]

Hi All,
I am trying to figure out if there is another formula rather than VLookup.
When you select column “A” on tab Test will automatically populate column B and C, taking information from the tab “new”.
To make the Vlookup works I hade to put in the first position the subcategory, changing the original layout. If I move subcategory in the 3rd column can I use another formula to populate the columns “Category” & “cost type” (I was thinking Index or Index match) but I am not very familiar with Index formula.
Thanks
Ale

[xor]

Is it like shown in the attached?

[paxon]

Originally Posted by xor

Is it like shown in the attached?

That's what I was looking for.

Could be give me a quick description of the formula & why you put a Zero "0" in the of the two formulas.

Thanks for your help

[xor]

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.

[paxon]

Thanks a lot for your help!!