INDEX MATCH problem

[majime01]

Hi all,

I have attached a spreadsheet with which I am having problems figuring out how to correctly use the INDEX MATCH function. When you select a lesson in J4, I want it to look at the table and return the associated values from Column B and also the associated values from Column C...the problem is that I cannot figure out how to return multiple values using an offset...meaning when you select lesson 1.2 it should return 2 values in Column K and two values in Column L.

Your guidance and expertise is appreciated.

Ken

[TheN]

Your number you are trying to match is stored as text. Also the syntax of your formula is incorrect.

Try this instead:

=INDEX($B$2:$B$7,MATCH(J4,$A$2:$A$7,0))

Furthermore, what are you asking for, please give sample desired results. I believe you will need either a helper column or an array formula. Since it sounds like you want to display both matches, unless of course you are wiling to use 2 separate formulas, assuming you only want to find a second match. What is your real data like?

[Phuocam]

A2:A7 is number.

J7 is text.

Try this in K4:

=INDEX($B$2:$B$7,MATCH(VALUE($J$4),$A$2:$A$7,0))

In K5:

=INDEX($B$2:$B$7,MATCH(VALUE($J$4),$A$2:$A$7,0)+1)

[majime01]

Thank you Phuocam for your input. Your formula works great, but I ran into another problem. Since the data I am working with is unit and lesson numbers for a text book, I have them in as text as in 1.1, 1.2, ... 1.9, 1.10, 1.11. This does not follow the rules of numbers, so they are text. When the formula runs, it works great until it hits 1.10 and above. Any ideas on how to correct? I have attached an updated file.

Thank you for your time,
Ken

[Phuocam]

Edit:

K4:

=IFERROR(INDEX($B$2:$B$7000,MATCH($J$4&"",$A$2:$A$7000,0)),INDEX($B$2:$B$7000,MATCH(VALUE($J$4),$A$2:$A$7000,0)))

K5:

=IFERROR(INDEX($B$2:$B$7000,MATCH($J$4&"",$A$2:$A$7000,0)+1),INDEX($B$2:$B$7000,MATCH(VALUE($J$4),$A$2:$A$7000,0)+1))

[majime01]

Amazing! Wow! I can't thank you enough for your help.

Ken

[Phuocam]

You are welcome!