picking column A values based on differences between members in column C

genetist

Board Regular
Joined
Mar 29, 2013
Messages
75
Office Version
  1. 2016
Platform
  1. Windows
Hi to all,
I have data like this,
Memberchrlocation
web150000
reb130000
seb1100000
keb1150000
geb1200000
deb140000

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

Now i want to select member column values based on the differences in values between members of location column should be 50000, it means if select web then my next selection should be SEB because next value (i.e web + 50000) is 100000 and it is matching to SEB in members column.

expected results
web150000
seb1100000
keb1150000
geb1200000

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

I need help to solve this problem and i tried with normal if formulas but not getting expected results. Any help would be appreciated,
Thanks in advance,
Regards,
Genetist
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Can you please be clearer about what behavior you need? In your example, each additional row of the output is further down the list. Do you want to review all list items when searching for the next one? For example, would this list give the same result:

geb 1 200000
web 1 50000
reb 1 30000
seb 1 100000
keb 1 150000
deb 1 40000

(Only change is geb comes first on the list.)

Do you want the list to stop when it can't find an entry even if there are additional options in the pattern? For example, would the following list end at seb or geb (keb at 150000 has been removed)?

web 1 50000
reb 1 30000
seb 1 100000
geb 1 200000
deb 1 40000

If there are any other conditions left unsaid, please describe them as well so that we can help...
 
Upvote 0
Hi Excel Tactics,
Thank you very much for your help towards my problem,
my data looks like this
Memberchrlocation
web150000
reb130000
seb1100000
keb1150000
geb1200000
deb140000

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

and for the rest of my data chr column will change to 2 then 3 like this upto 12, but now i want to stop my selection or drag my formula upto 1 in chr column then my dragging start from 2 in chr and then upto 12 in chr column because my data in other columns is specific to number in chr column. so now i do not want to change order of my members and chr column and wants to members in member column with in chr column=1 and members of location column should have difference 50000 between them.
final my expected data looks like this
Memberchrlocation
web150000
seb1100000
keb1150000
geb1200000

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

and like this for rest of chr column values (2 to 12).
Thanking you very much for your efforts.
Regards,
Genetist
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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