What function do I need? or what am I doing wrong? VLOOKUP? MATCH? or maybe INDEX?

Hi, I have this file (an attached example), and I'm trying to make a search function that returns the value to the left (column B) of the search column (column C) match. But why can't I get any returned value from my function? It seems that if a match is found it returns "#REF!", and if no match is found it returns "#N/A", but no value. In cell F3 the search term is entered, and in cell G3 I have entered the function that depends on F3. Does someone know how I can fix this? What function? I'm a total newbe, and my english isn't perfect either but I hope someone may understand what I am trying to do anyway.. Thanks in advance.
/Axel
example.xlsx

Originally Posted by Glenn Kennedy

Hi. The problem was that (unless you fiddle with it extensively) VLOOKUP doesn't run from R to L. INDEX-MATCH does. Try this:

=INDEX($B$2:$B$11,MATCH(F3,$C$2:$C$11,0))

I tried that function but it doesn't work when I search for example "604", but thank you anyway Think I have solved it now

Originally Posted by AlKey

Try this

=LOOKUP(10^308,SEARCH(F3,C2:C11),B2:B11)

Thank you so much, this works really well! So I have pasted it in my real document now and it work´s brilliant, but there is one thing I'm woundering; why "10^308"? I know it works but I am just curious

Originally Posted by AlKey

Try this

=LOOKUP(10^308,SEARCH(F3,C2:C11),B2:B11)

Hi again, there is one more thing, with this function, if I have not entered anything in F3, it returns the last entry in column B, is it possible to make it return nothing (blank), or maybe a "N/A"?
Thanks

Originally Posted by Glenn Kennedy

Thanks for the feedback - I didn't notice the multiple values for one entry in column B. WRT your last request - that's easy

=IF(F3="","",LOOKUP(10^308,SEARCH(F3,C2:C11),B2:B11))

Okay so I am using this now, thanks. I found an situation in my original document (can't upload for safety reasons) when there are multiple matches. In this function it returns one match, is it possible to ad something to the function that makes it return all matches, like; "604, 534, special"?
Thank you

Originally Posted by Glenn Kennedy

Thanks for the feedback - I didn't notice the multiple values for one entry in column B. WRT your last request - that's easy

=IF(F3="","",LOOKUP(10^308,SEARCH(F3,C2:C11),B2:B11))

Okay so I am using this now, thanks. I found an situation in my original document (can't upload for safety reasons) when there are multiple matches. In this function it returns only one match, is it possible to ad something to the function that makes it return all matches, like; "604, 534, special"?
Thank you

Originally Posted by Glenn Kennedy

Thanks for the feedback - I didn't notice the multiple values for one entry in column B. WRT your last request - that's easy

=IF(F3="","",LOOKUP(10^308,SEARCH(F3,C2:C11),B2:B11))

Okay so I am using this now, thanks. I found an situation in my original document (can't upload for safety reasons) when there are multiple matches. In this function it returns only one match, is it possible to ad something to the function that makes it return all matches, like; "604, 534, special"?
Thank you

Originally Posted by Glenn Kennedy

Where are the multiple matches, in the number column (a new requirement) or in the document column (already covered by your original post)? I'm not clear as to which eventuality you're tryig to cover...

In the number column, i posted the example file again but with new functions and longer list with multiple matches. I left a comment in the function cell. Sorry for compicating things.. :Pexample.xlsx

Originally Posted by Glenn Kennedy

Try htis. It's a bit clunky, but it works for your examples.

Okay thank you it seems perfect! Now I just have to modify it a little bit to make it work with the real document! Once again thank you so much, you've helped me a lot!

Originally Posted by AlKey

Hi accelgronvall,

If you have multiple values to return use this array formula

=IFERROR(IF($F3>0,INDEX($B:$B,SMALL(IF(ISNUMBER(SEARCH($F3,$C2:$C13)),ROW($C2:$C13)),COLUMNS($G:G))),""),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

When I'm trying to use this formula it only gives me one result?