query referring to an if / then, followed by subtraction.

[alreed96]

okay, I created a spreadsheet that has a group of numbers from 1-50...what i am trying to do is find out when a particular value has shown up and subtract it from the value that is in another cell. the set up is as follows:

B2 = value

E54:E194 = array of values where [value] can be found

L54:L194 = number of day since [value] was last used

i want to know what formula would work by taking above information and being able to find [value] in array of values then subtracting [value] from day since [value] was last used. Note: the [value] that I'm looking for would be in the same row as the [value] i want to subtract it from, only in a different column. What I eventually want to do is copy and paste said formula down the list of values, so that each value has a another value next to it stating when that value was last used.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=B2-INDEX($L$54:$L$194,MATCH(B2,$E$54:$E$194,0))

 

[alreed96]

=B2-INDEX($L$54:$L$194,MATCH(B2,$E$54:$E$194,0))

well, it seems that my B2 value is off by a couple of cells., but it did do the trick, i just need to figure out why my value doesn't match with the array and subtraction values.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[alreed96]

okay, finally figured where the problem was, putting the ='B2-' at the start of the formula, wasn't needed. Once I removed that from the formula, everything else went smoothly, thank you so much for the assistance. You guys are tops.

 

[alreed96]

do i close this thread or do you guys do that.

 

[Fluff]

No, we don't close threads here.