Array to do Hlookup

[helpexcel]

How would I code an array to do a kind of hlookup? I have data on sheet1 in columns A:I. I have data on sheet2 in column A. I want to look at the value on sheet2 row A and see if it is listed in the data on sheet1 columns A:I. If it is, i want the value from row 3 of the column it's in on sheet1 to appear in column B of Sheet2.

Is this doable?

 

[AhoyNC]

Something like this?

Excel Workbook
	A	B
1	test3	3
2	test10	NoMatch
3	test2	2
4	test8	8
Sheet2

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	test1	test2	test3	test4	test5	test6	test7	test8	test9
2
3	1	2	3	4	5	6	7	8	9
Sheet1

 

[helpexcel]

Thanks! the sheet2 columns would be flipped. So you would be looking for the numbers and returning the test#.