Sum product not working

[kocumna]

[h=2]

=sumproduct not working[/h]

Good afternoon,
I have two spreadsheets located in two different workbooks with several columns, one of them being a Claim Number column with values that look like this: ER570W25600, none of the claim numbers are the same and a net savings column (they are not right next to eachother).

On one spreadsheet , I need to match the claim number with the claim number from the other main spreadsheet and once I find the match, copy the net savings from the first spreadsheet and put it into the net savings column from the column on the bigger spreadsheet. The problem is each claim number on the big spreadsheet ends with 00 and are different rows, but the spreadsheet that I am pulling from, the claim numbers could end in 01, 02, and so on.

I tried =sumproduct, but it doesn't seem to be working. Could you please help?

Thanks in advance.
Nadine​

 

[Oscar Cronquist]

Array formula:


=INDEX(Net_savings_column, MATCH(LEFT(Claim_number_other_cell, LEN(Claim_number_other_cell)-2),LEFT(Claim_number_column,LEN(Claim_number_column)-2),0))

Does this make sense to you?

Net_savings_column - A cell reference to the net savings column
Claim_number_other_cell - A cell reference to the claim number you want to find
Claim_number_column - A cell reference to the claim number column

 

[kocumna]

Array formula:


=INDEX(Net_savings_column, MATCH(LEFT(Claim_number_other_cell, LEN(Claim_number_other_cell)-2),LEFT(Claim_number_column,LEN(Claim_number_column)-2),0))

Does this make sense to you?

Net_savings_column - A cell reference to the net savings column
Claim_number_other_cell - A cell reference to the claim number you want to find
Claim_number_column - A cell reference to the claim number column

Hi Oscar.

Thank you so much for replying. When a reference "A cell to the net savings column", do I just use one cell or can I use a range of cells, because Iam trying to search for more than one claim? Also "(Claim_number_column,LEN(Claim_number_column)-2),0))" Is this telling me to highlight the whole claim number column that I am trying to match the claim number too, twice?

Thanks in advance.

Nadine

 

[kocumna]

Hi Oscar,

I see that you put in LEN to have claim number return, but I need to have the net savings amount returned and put into the net savings column/row that the claim number matches. Hopefully that makes sense.

Thanks again.