VLOOKUP, MATCH, INDEX Result?

[colindk]

Hope someone can help... I have a workbook with 3 sheets: Stats,
Company, Billing.

I want the cell B1 in my Stats sheet to look at the content of the cell
in H2 of the same sheet.
It then needs to match the content of H2 with the first instance of
that in the Billing sheet.
Then from the same row, 4 columns along, take the Company_ID in the
Billing sheet and match it with the Company_ID in the Company sheet
Once matched it should bring back the Company name and address. Also
both the Company and the Billing sheet have expanding records.

I have been looking at using VLOOKUP with INDEX and MATCH, but cannot
seem to make it work. I have included a sample file...

I had previously been given this function, but it was bringing the
wrong company name back:

=VLOOKUP(OFFSET(order,MATCH($H$2,OrderID,0),3),CoDetails,2,FALSE)

where
CoDetails is =OFFSET(Company!$A$1,0,0,COUNTA(Company!$A:$A),5)
Billing is =Billing!$A$1
OrderID is =OFFSET(Billing!$A$1,0,0,COUNTA(Billing!$A:$A),1)




------------------------------------------------




------------------------------------------------

 

[colindk]

Oops forgot to attach the file...

File Attached: http://www.exceltip.com/forum/attachment.php?postid=32734 (lookup_info.xls)

------------------------------------------------




------------------------------------------------

 

[acw]

Colindk

Using the data in your sample workbook...

In B1 put in the formula
=VLOOKUP(H2,Billing!A2:E7,5,FALSE)
In B2 put in the formula
=VLOOKUP(B1,Company!B2:C4,2,FALSE)


HTH

Tony