Need VLOOKUP to find a value, then subtract a value

[gmaboing]

I've been working on this for two days now. I have a very long nested "If Then" statement that does what I want but I want a shorter way to get the answer. Here's the scenario:


Below in column B & D, I have "Names" and in column C & E, I have assigned a value to each name (I'm using spaces between the columns to try and line up the values)...



COLUMN B...............COLUMN C..........COLUMN D..........COLUMN E
Alex...........................20....................Brad....................77
Brad..........................15....................Paul.....................24
William.........................9....................Ron.......................3
Andrew.......................74....................Leslie..................79


I would like to see if D1 (Brad) exists in B1:C4 (basically all of columns B & C ) and if D1 (Brad) exists in columns B, take the value assigned to B1 (15,) and divide it by the corresponding value found in D1 (77) and subtract 1.

Would I be correct to use a VLOOKUP, and if so how?

Thank you for your help in advance.

 

[VoG]

Try

=IF(ISNA(MATCH("Brad",B:B,0)),"",INDEX(E:E,MATCH("Brad",D:D,0))-INDEX(C:C,MATCH("Brad",B:B,0))-1)

 

[T. Valko]

I've been working on this for two days now. I have a very long nested "If Then" statement that does what I want but I want a shorter way to get the answer. Here's the scenario:


Below in column B & D, I have "Names" and in column C & E, I have assigned a value to each name (I'm using spaces between the columns to try and line up the values)...



COLUMN B...............COLUMN C..........COLUMN D..........COLUMN E
Alex...........................20....................Brad....................77
Brad..........................15....................Paul.....................24
William.........................9....................Ron.......................3
Andrew.......................74....................Leslie..................79


I would like to see if D1 (Brad) exists in B1:C4 (basically all of columns B & C ) and if D1 (Brad) exists in columns B, take the value assigned to B1 (15,) and divide it by the corresponding value found in D1 (77) and subtract 1.

Would I be correct to use a VLOOKUP, and if so how?

Thank you for your help in advance.

Try this...

=SUMIF(B$1:B$4,D1,C$1:C$4)/E1-1

Assumes the name will appear in column B only once.

 

[gmaboing]

Try this...

=SUMIF(B$1:B$4,D1,C$1:C$4)/E1-1

Assumes the name will appear in column B only once.

Biff, yes, the name will appear only once.

Thank you everyone for respoinding. I will try and let you know what I get.

 

[VoG]

I misread the question. Mine should have been

=IF(ISNA(MATCH("Brad",B:B,0)),"",INDEX(E:E,MATCH("Brad",D:D,0))/INDEX(C:C,MATCH("Brad",B:B,0))-1)

 

[gmaboing]

Biff, yes, the name will appear only once.

Thank you everyone for respoinding. I will try and let you know what I get.

(#DropsHerHeadInEmbarassment&Shame)...

Everyone,
I'm not quite sure if I'm doing something wrong (and I usually am) but I'm going to try and reclarify this using the true values that I'm working with:
*note that cols A & B are the current values and C & D are last month's values. I'm trying to find the variance increase or decrease.


CURRENT STATS.............LAST MONTH'S STATS.............%age (+ or -)
(A)............(B).................(C)..........(D)......................(E)
AUT........14,143.................AUT........40,153.................-64.78%
ABN........16,284.................ABN........16,284...................0%
ABS........82,123.................ABS........20,153..................307.50%
XYT........40,153.................AXM........85,875.................-53.24%
AZA........29,439.................AZA........29,439...................0%

I am looking for A1 (Aut) in col (C). If A1 is found, compare the number of claims from the previous month to the number of claims we received this month and show the variance (increase or decrease.)
Based on this logic I should be (am trying to do) this:

(14,143 / 40,153)-1 = -64.78% (indicating we had a decrease in claims this month when compared to last month)


I am checking A1 against all of column C because there could be new data provided in columns A and B that would throw the allignment off; otherwise I would just compare B1 to D1.

Again, I apologize if I could have worded my original post in a clearer manner. Hopefully this is better.

 

[T. Valko]

(#DropsHerHeadInEmbarassment&Shame)...

Everyone,
I'm not quite sure if I'm doing something wrong (and I usually am) but I'm going to try and reclarify this using the true values that I'm working with:
*note that cols A & B are the current values and C & D are last month's values. I'm trying to find the variance increase or decrease.


CURRENT STATS.............LAST MONTH'S STATS.............%age (+ or -)
(A)............(B).................(C)..........(D)......................(E)
AUT........14,143.................AUT........40,153.................-64.78%
ABN........16,284.................ABN........16,284...................0%
ABS........82,123.................ABS........20,153..................307.50%
XYT........40,153.................AXM........85,875.................-53.24%
AZA........29,439.................AZA........29,439...................0%

I am looking for A1 (Aut) in col (C). If A1 is found, compare the number of claims from the previous month to the number of claims we received this month and show the variance (increase or decrease.)
Based on this logic I should be (am trying to do) this:

(14,143 / 40,153)-1 = -64.78% (indicating we had a decrease in claims this month when compared to last month)


I am checking A1 against all of column C because there could be new data provided in columns A and B that would throw the allignment off; otherwise I would just compare B1 to D1.

Again, I apologize if I could have worded my original post in a clearer manner. Hopefully this is better.

Something like what VoG suggested should work.

What version of Excel are you using?