My If statement not working

**rlkerr** · 12-03-2007, 04:51 PM

I have a worksheet with lots of data, and I'm trying to pull out certain rows.

I made a new sheet to contain all the items I need. Sheet Types has a list of items. And, Spend Data has all my data.

If column H7445 on spend data has one of the items in Types column C, then put whatever is in column A4112 on Spend Data, etc. etc.

Here is the formula that has worked on other projects but it's not working now.

=IF(ISNUMBER(MATCH('Spend Data'!$H7445,Types!$C:$C,0)),INDEX('Spend Data'!A7445,MATCH('Spend Data'!$H7445,Types!$C:$C,0)))

**NBVC** · 12-03-2007, 05:07 PM

You probably want:

Please Login or Register  to view this content.

EDIT: Changed Index Sheetname: Should be "Types"

**daddylonglegs** · 12-03-2007, 05:08 PM

Your INDEX range needs to be a range of cells the same size as the MATCH range, you only have a single cell. Try

=IF(ISNUMBER(MATCH('Spend Data'!$H7445,Types!$C:$C,0)),INDEX(Types!$A:$A, MATCH('Spend Data'!$H7445,Types!$C:$C,0)),"")

**rlkerr** · 12-03-2007, 06:23 PM

I tried that statement. I do get data other than #ref, but it's not pulling the correct data.

I have attached an example of my file.

You can see that on FIMS sheet on line 4 it looks for Corrosion Inhibitor on Types column C. However, if you look on spend data sheet it pull the wrong data in column B. It should be Wesley, but it's pulling Pennington and the wrong year in column C.

My goal is if column H is any of the item in Type column C then pull all the information on that row to the FIMS sheet.

**rlkerr** · 12-03-2007, 06:37 PM

I looks like to me it's finding the first instance of Corrosion inhibitor and pulling that information

**rlkerr** · 12-04-2007, 10:18 AM

On the FIMS sheet, you can see that it is pulling the first thing it see.

Look at row 2, it found H3 in column c of Type(sheet), and even pull the correct information, but it did this for every row. It's not pulling the data over that is associated with that row.

I need for it to look at Column H and if you find that in column C of Type then pull that whole row over.