Formula Question

[ericbss7]

Hello, I'm trying to create a formula that will search for a matching value in the spreadsheet, and then do a subtraction calculation. Example:

A B
1 Bob
2 Chris
3 Mike
4 John
5 Shawn


C D
1 John
2 Mike
3 Chris
4 Shawn
5 Bob

What I need is to find the difference between the two places a certain name is listed, and display in column 'E'. For example, in column 'E' next to Bob's name, I need it to equal a 4, since 5-1 is 4. Next to Chris in column 'E' would be a 1, since 3-2 is 1.

Thanks for any help.

 

[Aladin Akyurek]

Try...

=INDEX($C$1:$C$5,MATCH(B1,$D$1:$D$5,0))-A1

 

[Silvrhelm]

Shouldn't you sort the list by names (column B and then by column D) then all the names will be next to each other and you an just do in E1 =A1-C1 and drag it down all the entries.

And if you really want it in number order after that you can just reorder by columns A and C

 

[ericbss7]

Try...

=INDEX($C$1:$C$5,MATCH(B1,$D$1:$D$5,0))-A1

After trying it, this is the right idea for sure, the only issue is the subtraction. What you listed works for Bob, because his numeric value in the first list is in cell A1.

For the others, this obviously won't work because it will just subtract 1 instead of what their numeric A column value is.

I know to fix this, I would just replace the A1 with whatever value their name has in Column A, but I'm trying to avoid having to change the formula constantly, as this will be a constantly changing spreadsheet with new names and new orders every week.

Is there any way to do this? Thanks.

 

[maheshmaxi]

Great Formula Aladin!

 

[Aladin Akyurek]

After trying it, this is the right idea for sure, the only issue is the subtraction. What you listed works for Bob, because his numeric value in the first list is in cell A1.

For the others, this obviously won't work because it will just subtract 1 instead of what their numeric A column value is.

I know to fix this, I would just replace the A1 with whatever value their name has in Column A, but I'm trying to avoid having to change the formula constantly, as this will be a constantly changing spreadsheet with new names and new orders every week.

Is there any way to do this? Thanks.

Did you enter the formula in E1 and copied down?

 

[ericbss7]

Did you enter the formula in E1 and copied down?

Excellent point, forgot about that function. Thanks a lot this is great!

 

[Aladin Akyurek]

Excellent point, forgot about that function. Thanks a lot this is great!

You are welcome. Thanks for providing feedback.