matching a value from one sheet and returning a value from a row in sheet 2

Your formulas don't work because they can't handle the errors in the other sheet. I added some errorhandling and now it works.

The second workbook is how I would do it. Using Excel's Table feature makes it much easier to maintain tables with varying size. No need to update formulas when new rows are added. Also, when adding new rows the table expand automatically and formulas are copied along.

Go to the Insert tab, second from right is Tables. This will convert any list or table you are standing in to a Excel Table. The names are generated automatically and are based on the names of the headers in the table. The name of the entire table (Table1 Table2 etc) are generated automatically by excel.

To change the entire table name, select any cell in the table. At the very top of your screen there is Table Tools and under that Design. Click that. To the very left is a field where you can type in any table name you want.

You can refer to different parts of the Table by a naming convention. Since the whole thing expands automatically these names are similar in function to dynamic named ranges. There is a naming convention that tells you how to refer to the different parts of the Table as described in the links below. Don't worry if you find that too boring to read, you don't have to.
In real life you simply select the parts you are interested in when typing the formula and the correct name reference will be inserted.
Make a simple formula just to test and you will see what I mean. Or just type = in a cell outside the Table and select a column and you'll see.

The following parts are named and can be referred to(just what I can think of now):
- The entire header row
- One header cell
- The entire table, including headers
- The entire table, without headers
- A entire column or columns, with headers
- A entire column or columns, without headers (very useful)
- Any cell that is on the same row as you type the formula (very useful)

Some boring links:
http://office.microsoft.com/en-us/ex...010048546.aspx
http://www.techrepublic.com/blog/10-...-table-object/
http://www.jkp-ads.com/articles/Excel2007tables.asp

Originally Posted by Jacc

Go to the Insert tab, second from right is Tables. This will convert any list or table you are standing in to a Excel Table. The names are generated automatically and are based on the names of the headers in the table. The name of the entire table (Table1 Table2 etc) are generated automatically by excel.

To change the entire table name, select any cell in the table. At the very top of your screen there is Table Tools and under that Design. Click that. To the very left is a field where you can type in any table name you want.

You can refer to different parts of the Table by a naming convention. Since the whole thing expands automatically these names are similar in function to dynamic named ranges. There is a naming convention that tells you how to refer to the different parts of the Table as described in the links below. Don't worry if you find that too boring to read, you don't have to.
In real life you simply select the parts you are interested in when typing the formula and the correct name reference will be inserted.
Make a simple formula just to test and you will see what I mean. Or just type = in a cell outside the Table and select a column and you'll see.

The following parts are named and can be referred to(just what I can think of now):
- The entire header row
- One header cell
- The entire table, including headers
- The entire table, without headers
- A entire column or columns, with headers
- A entire column or columns, without headers (very useful)
- Any cell that is on the same row as you type the formula (very useful)

Some boring links:
http://office.microsoft.com/en-us/ex...010048546.aspx
http://www.techrepublic.com/blog/10-...-table-object/
http://www.jkp-ads.com/articles/Excel2007tables.asp

I see that it works on the examples you gave. I copied the formulas over to my main sheet and I get an error. I have included the entire file this time. I really appriciate the help.

Got it now thanks. My own editing errors

You haven't copied the same formula throughout the two columns. Find a place in the middle of the column where you know the formula works and then just copy it all the way up and down.
Normally when you change a formula at the first row in a table it will instantly copy itself down the whole column. Dunno if there is a setting that determines that or something. Or maybe you only need to have the same throughout the columns to trigger it.