vlookup with wildcards

[Patrunjel]

Hi,

I am trying to do a vlookup for a number that has one decimal. The number will always be "5" but the decimals can be from 0-9 (e.g. 5.2, 5.0, 5.9)
I tried to concatenate vlookup(5&".'&"*",table,4,false) but it doesn't work.
I cannot vlookup a cell (such as A3 or B4), bc I am looking for the first occurance of a "5" in a list.

Your expert help is greatly appreciated!

Thank you.

 

[Vidar]

Hi
Try this:
=INDEX(table,MATCH(5,ROUNDDOWN(table,0),0))

Vidar

 

[Patrunjel]

Hi
Try this:
=INDEX(table,MATCH(5,ROUNDDOWN(table,0),0))

Vidar

Thank you Vidar, but I get an error (#value). I don't thing rounddown works with an array. Or perhaps I'm wrong.

 

[Robert Mika]

Hi,

I am trying to do a vlookup for a number that has one decimal. The number will always be "5" but the decimals can be from 0-9 (e.g. 5.2, 5.0, 5.9)
I tried to concatenate vlookup(5&".'&"*",table,4,false) but it doesn't work.
I cannot vlookup a cell (such as A3 or B4), bc I am looking for the first occurance of a "5" in a list.

Your expert help is greatly appreciated!

Thank you.

Two solution.
One based on vidar example:

Excel 2010
	E	F	G
1
2	5.2		5.2
3	5		5.1
4			5
Sheet10

Cell Formulas
Range	Formula
E2	{=INDEX(G2:G4,MATCH(5,ROUNDDOWN($G$2:$G$4,0),0))}
E3	{=VLOOKUP(5,ROUNDDOWN($G$2:$G$4,0),1,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Vidar]

Thank you Vidar, but I get an error (#value). I don't thing rounddown works with an array. Or perhaps I'm wrong.

I'm sorry, forgot to tell you that you need to hit Ctrl+Shift+Enter since it is an array formula.
Vidar

 

[Patrunjel]

Thank you SO MUCH both! It worked!!!

 

[marka87uk]

Why not just use a VLOOKUP with range_lookup set to TRUE?

If it can't find the value exactly (e.g. 5.8) then it'll use the largest value which is less than it (in this case 5).

=VLOOKUP(A1,LookupTable,2,TRUE)