Formula Help

[ringrim]

Good Morning,

Am having a bit of trouble coming up with the correct formula here at work. I am new to Excel Formulas and am hoping someone can help. I have attached my workbook that has 2 sheets. In worksheet Totals i have column F titled CPT. I need a formula that will match the values from each cell in column F to my worksheet titled CPT using column 1. If the match is found, return the value from worksheet CPT column 2. I thought this would be easy but my following formula isnt correct. Can anyone help me out?

=INDEX(CPT!$a$5:$a$511,MATCH($f$8,CPT!$b5:$b$511,0),2)

Thanks

[NBVC]

First,

It seems the numbers in Column A of CPT are actually formatted as text.

Fix this by selecting column A and go to Data|Text to Columns and click Finish.

Then you can use a Vlookup() formula for what you are doing...

Try, in P5 of Totals sheet: =VLOOKUP(F5,CPT!$A$5:$B$511,2,0) copied down.

[ringrim]

That worked great.

Is there a way to do that for all 1700 rows? Or do i have to paste it one by one?

[NBVC]

copy it down.

Select cell P5 where you entered the formula. Then click and hold the little black square at the bottom right corner of the cell and drag it down as far as you need... the formula will copy and update as necessary.