Retrieving number for calculation: VLOOKUP, LOOKUP, INDEX/MATCH or ????

LakeDog

New Member
Joined
Oct 18, 2017
Messages
18
Office Version
  1. 365
Platform
  1. Windows
First of all, thank you all for taking your time to share your expertise. It is appreciated. This is my first time posting, so apologies if I do something wrong. I've read the FAQ, so hopefully I won't!!

I have a worksheet that starts with several columns of numeric data that has about 2000 rows. That data is used to generate several columns of calculations. Two columns of calculations are similar and close to each other in value and actually the values "cross over" each other from time to time. Meaning one columns values becomes greater than the other, then later, the values in the second column increase and at some point are greater than the first. I have a formula that signals "alpha" when column 1 exceeds column 2, then "bravo" when column 2 >= column 1. That is all great, but the next step I want is tripping me up. Associated with each alpha/bravo is an original data value. I want to be able to subtract that number associated with alpha from the next bravo number in sequence. This is a stripped-down example to just give you a better idea of what I am asking.

A B C D
4
5 Bravo 5 -2 (5-7)
3
2
7 Alpha 7
5
3
9 Bravo 9 3 (9-6)
8
5
6 Alpha 6
9
4
2

I have tried a variety of VLOOKUP formulas, LOOKUP formulas and have not been able to consistently pull that number associated with alpha or bravo. The values are not sorted, nor can they be. Suspect it is syntax, but at this point I am unable to think clearly on it. Trying to learn INDEX and MATCH, but just learning. Appreciate any thoughts, insights or suggestions. Thank you!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I should mention that the last formula I was trying was:

=IF (B2="bravo",C2-VLOOKUP(B2,B3:C5000,2,false),"")

At this point, I'm so fried, couldn't tell you how I got here! LOL
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top