Can I use a formula to identify WHICH row contains specific data?

[ExcelTrouble]

I have two worksheets.
The first sheet contains columns for:
Clients,Costs,Revenue,Act.Revenue,Hours,Hourly

This sheet has a lot of client's history, many of which are no longer active


The second sheet, has the same columns
but only with active customers and only their information for the current
year.

I am looking for a formula that I can put in Sheet 2, in a blank column
after the hourly column, which will search to see if there is a matching
client in Sheet 1, and if there is - copy that client's information into
Sheet 2, right beside the information for that client. This way I will end
up with two reports side by side for the same client. One showing their
current activity, the other showing their past history.

Can anyone help me with this? I get lost with all the $'s, commas, words,
and brackets

Thank you!

 

[Max]

One way via index/match

Assume data in cols A to F in both Sheet1/2, from row2 down
where the key match col = col A (Client ID?)

In Sheet2,
Put in say, H2
=IF(ISNA(MATCH($A2,Sheet1!$A:$A,0)),"",INDEX(Sheet1!B:B,MATCH($A2,Sheet1!$A:$A,0)))
Copy across to L2, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400 Files:361 Subscribers:58
xdemechanik

 

[Ashish Mathur]

Hi,

You could also use the VLOOKUP() function. This function is well explained
in Excel's help menu.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com