Can you use Vlookup to search a entire spreadsheet for a code and then return the QTY

Here is the situation using Excel 2007, I have a CODE and QTY in the top row with the code and qty values below on the first page/tab. On the second tab I have many fields names in the top row with different values below them (a suppliers stock update that has descriptions, notes, numbers, etc) .

Can you use Vlookup to search a entire spreadsheet on the second page/tap for the CODE and then if found somewhere find the coresponding QTY on the same row but find the name QTY at the top so that it will use that particular column ? hope I made it clear enough if not I can do some pictures to make it easier.

I assume it can't do this alone and if anyone can point me in the right direction that would be great, thanks.

Your best bet would be to upload a sample workbook, rather than images. I would imagine it can be done quite easily, but can't really give a specific example without some data.

I have uploaded a sample (allot of stuff removed but the same applies).

On Page1 the Code and Qty will stay in the same position but on Page2 the Code and Qty will be in different positions on different spreadsheets but the names will remain the same.

I know I could just copy and paste the Qty and Code on Page to into two cells and use the Vlookup function which is quite simple but I thought I could cut out an extra step by just having it search the entire spreadsheet as previously mentioned.

Thanks for the help.

As mentioned, we can help you out better with a sample workbook.

From what I can imagine from your post, I believe you can accomplish your task using a combination of the INDEX and MATCH functions.

how about this?

Hi Cookstein,

Unless someone else is around to help right now, could you please post a copy of the workbook in .xls compatability mode. I unfortunately do not have Excel 2007 on my work PC. If not (and if no one else has answered this question), I can take a look once I get home later today.

OK, here you go,

there must be a reason work places dont have Excel 2007. I dont have it at work either......

Will code always be in Col B and Qty in Col G?

If so, you can still use VLOOKUP:
=VLOOKUP(A2,Page2!B:G,6,FALSE)

Or, so long as the code is always in col B it doesn't matter where the QTY is by using:
=INDEX(Page2!$1:$65536,MATCH(Page1!$A2,Page2!$B:$B,0),MATCH(Page1!$B$1,Page2!$1:$1,0))