returning a string from an array when given 2 references

[crywolf]

I want to collect the string in row 1 when given a value in col 1 and a value that would exist in that row.
For example if given a 4 for col 1 and a value of 10, I want the string 18-2 returned.
The data in rows 2 thru 10 is self entered and can be changed if it would help.
Many thanks for any help provided.

asas.png

[mehmetcik]

How would you specify the 4 and where would your output go?

[Root_]

With 4 in cell H1 and 10 in cell I1, try

=INDEX(B1:F1,MATCH(TRUE,INDEX(ISNUMBER(FIND(","&I1&",",","&INDEX(B2:F10,MATCH(H1,A2:A10,0),0)&",")),0),0))

[crywolf]

They are both variables.
rank = 4
points = 10
result = 18-2

all 3 are strings

Thanks for looking

[crywolf]

Thanks Root_

Now I should be able to assign that to a var in VBA, correct ?

ie var1 = index.........

[Root_]

Not exactly.

You would need var1 = [INDEX(...)]

[crywolf]

asasas.png

here is a pic of the sheet named DATA

the VBA formula I am trying is pasted in row 20.

the actual code is

Private Sub Score_ComboBox_Change()
Score_ComboBox.BackColor = &H80000005

loser_balls_made = Score_ComboBox.Text

'score change

Splitpoints = [INDEX(poss_scores,MATCH(TRUE,INDEX(ISNUMBER(FIND(","&loser_balls_made&",",","&INDEX("&loser_points&",MATCH("&losers_rank&","&loser_rank&",0),0)&",")),0),0))]


'my_points = Left(Points, 1)
'opp_points = Right(Points, 1)
'points_TextBox.Value = RetPts

End Sub

I am getting an error 2015

Please help
Thank you

[crywolf]

I am getting an error 2015

Please help
Thank you

[MrShorty]

Observation -- I notice that the working left to right across a given row, the numbers are continuously increasing, so that each column represents a range. For the "4" row, B is 0 to 5, C is 6 to 8, D is 9 to 11, and so on. Similar for each row, except that the boundary value between columns is different. Question -- Is this going to always be true?

If so, then the solution I see is to enter only one value (the initial value) in each cell and use the "approximate match -- sorted ascending" option in Excel's MATCH() function. https://support.office.com/en-us/art...9-533f4a37673a

In a simple worksheet formula (I don't know why VBA is needed or desired for this), this might look something like:
row number: MATCH(4,$A$2:$A$10,1)
column number: MATCH(10,INDEX($B$2:$F$10,result of row number,0),1) -- recall from help file how INDEX() behaves when row or column # is 0 (it returns the entire column/row https://support.office.com/en-us/art...2-b56b061328bd ).
final result: =INDEX($B$1:$F$1,result of column number).

If there is a reason this wants to be done in VBA, use the same approach using the application.worksheetfunction object to access these functions in Excel's function library https://msdn.microsoft.com/en-us/vba...n-visual-basic

will that work for you?

[Root_]

See if this works for you

Please Login or Register  to view this content.

P.S.: I have removed the inner INDEX. It was used to make the formula in Post # 3 "normal-entered" (i.e., non-array).