Match function

[csleong]

Okay, I was trying to use an Offset function to locate a value, but it fails. Thus I check the formula and found the MATCH function doesn't return the location I want.

Refer to the attached caption picture, I make Row33 to be +1 from Row32, when I use the MATCH function, I expect the return value to be 1 to 10 from cell C34 to L34, but it wasn't... can anyone explain this to me?

I always lost when the data is not sort in order, or with a blank cell (="" or =" ") or an EMPTY cell, or duplicated data... I look for INDEX/OFFSET/MATCH combination but still, the answer always not there...


Capture.PNG

[Mysore]

Can you please upload your sample file in excel.

[Special-K]

Post a spreadsheet not a picture.

[csleong]

Please refer to the attach file.
1.xlsx

[Mysore]

You are lookup value is not exactly the same as in your Table array. By omitting the match type,
you are trying to identify the approximate match of your lookup value. Not sure what you are looking for.

[csleong]

I am using an Index/Match but I simplified the problem.

In actual, I am trying to check if my standard design size (table array) can fully utilize the space given (match value). Thus I need to use this function to find the largest value that is less than or equal to the match value.

[Mysore]

I'm sorry mate, I'm still unable to understand what you are trying to do. What is the output you want to see?

[azumi]

@csleong
Maybe better if you describe the expected results in your file, the menber is confused when the description is unclear.

Regards

[AlKey]

Can you show on your spreadsheet what the expected result should be?

[csleong]

I re-write the excel, please take a lot. Thank you.

2.xlsx

[Teethless mama]

=MAX(INDEX(($B2:$K2<=B4)*$B2:$K2,))

copy across

[FDibbins]

Im not ssure I really understand what you want, but you left out the last argument in your MATCH....
=INDEX($B$2:$K$2,MATCH(B4,$B$2:$K$2,1))

The "1" tells it to look for a value less than

[csleong]

Thanks Teethless, that's the result I am looking for.

However, I do not understand what's wrong with my equation, and too I not understand how Index(($B2:$K2<=B4)*$B2:$K2,) work? Just to learn.

Also thanks everyone for helping me on this problem. I am really appreciate.