formula to obtain next value in row?

[jw01]

hello

i need a formula that will show me the next value in the row if the lookup name is the same.

for example, the name 3HM occurs i.e. 30 times but they have different values in each row. once the user selects it from drop down in cell B2, then the cells in C5:C12 (red cells), should change accordingly.

the data should change with the numbers found in the "Data" sheet column i.e. AO.

i have attached a sample...thank you!

essentially, vlookup only grabs the first match of #s regardless of how many times it is located....but i want a formula to show the different values....hope it makes sense.

[jw01]

any thoughts on this guys?

[popipipo]

Vlookup works only with unique values.
3HM is not unique.
You have to do a addition to make it unique

[jw01]

Vlookup works only with unique values.
3HM is not unique.
You have to do a addition to make it unique

yes, i am aware of this .....i know there is a formula index, small etc but not sure how to compute that...thanks.

[FDibbins]

Looking at your data...how is excel supposed to know that you want the 1st...or 3rd...or 20th instance of that selection? Excel sees a criteria, and finds stuff based on that. If you can create a unique ID for each for each instance, then you can use that to pull in the different values. Maybe something like =C9&COUNTIF($C$9:C9,C9)
This will give you 3HM1, 3HM2, 3HM3 etc for each type

[benishiryo]

hi jw01. it's not impossible, like FDibbins mentioned, but isn't it torturing to you or the user to pick the the nth selection? if you insist, you can do this array formula in C5:

Please Login or Register  to view this content.

otherwise, another alternative is naming those validation list properly. those with repeated values will have a number tagged to them. for eg, somewhere in Calc Graph row 66, put in:
=IF(COUNTIF($B$66:$B$1256,B66)>1,B66&"_"&COUNTIF(B$66:B66,B66),B66)

copy down. copy this range of values & Paste Special -> Values into B66 onwards. so 3HM will become 3HM_1, 3HM_2, & so on. those with no repeats will turn up like the original. with that, the formula in C5 can be:

Please Login or Register  to view this content.

[jw01]

hi jw01. it's not impossible, like FDibbins mentioned, but isn't it torturing to you or the user to pick the the nth selection? if you insist, you can do this array formula in C5:

Please Login or Register  to view this content.

otherwise, another alternative is naming those validation list properly. those with repeated values will have a number tagged to them. for eg, somewhere in Calc Graph row 66, put in:
=IF(COUNTIF($B$66:$B$1256,B66)>1,B66&"_"&COUNTIF(B$66:B66,B66),B66)

copy down. copy this range of values & Paste Special -> Values into B66 onwards. so 3HM will become 3HM_1, 3HM_2, & so on. those with no repeats will turn up like the original. with that, the formula in C5 can be:

Please Login or Register  to view this content.

hey there

the first formula works amazing...thank you so, so much!

[arlu1201]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.