Excel Lookup based on drop down value?

Question

I have an Excel workbook with two tabs, the firstsheet (Sheet1) has two columns, one is a drop and the other is a number. The drop down has the three values below:

Dropdown Value    Number
Test1             XX
Test2             XX
test3             XX

Sheet 2 has the following Columns and data:

Test1   Test2   Test3
1           3   4
3           1   5
7           2   9

11          6   18    --- Totals

I am trying to write a formula that returns the total above based on the value selected in sheet1 from the dropdown. I have tried v/hlookup but no luck.

Answer 1

Use:

=HLOOKUP(A2,Sheet2!$1:$6,6,False)

Where row 1 has the titles and row 6 has the totals.