Partially working VLOOKUP derived from INDIRECT/CONCATENATE/& ?

[kae0r]

OK, I have a weird one...

I need to create a text string from 3 values and then put them together and look them up using a vlookup.

It works, kinda, it finds some values and not others and there's no seemingly difference between them!! Here's example

User selects from 3 values from dropdown box - Single, Dual or Multi

I do this for 2 other drop down box's (Telephone/email and some values 50000, 100000 etc) then create a text string:

=I16&O38&O44

or

=CONCATENATE(I16&O38&O44)

or

=(I16&O38&O44)

or

=N13

All these work to create the text string, e.g.: EmailSingle100000 or EmailMulti50000 or TelephoneDual250000.

I then VLOOKUP the string using this VLOOKUP:

=VLOOKUP(N13,S10:T129,2,FALSE)

I've also tried:

=VLOOKUP(CONCATENATE(I16&O38&O44),S10:T129,2,FALSE)

The vlookup tables looks like this:

EmailSingle50000	£43.00
EmailSingle100000	£36.00
EmailSingle250000	£29.00
EmailSingle500000	£22.00
EmailSingle1000000	£18.00
EmailSingle5000000	£15.00
EmailSingle10000000	£10.00
EmailSingle10000001	£8.00
EmailMulti50000	£86.00
EmailMulti100000	£72.00
EmailMulti250000	£58.00
EmailMulti500000	£43.00
EmailMulti1000000	£36.00
EmailMulti5000000	£30.00
EmailMulti10000000	£20.00
EmailMulti10000001	£16.00
...

Now it will work for some values like TelephoneSingle50000, but not TelephoneDual50000... im so confused.

Cheers,
Kyle

Any ideas?

 

[Robert Mika]

Hi Kyle and welcome to the board.
Have you checked if in fact those two string match?

You can, let say in cell A2 put A1= "cell with not working match".
The reason behind this is to check if they realy match.
If your answer will be FALSE this mean that there may have been leading or trailing spaces.
Give it a go and let us know the results.

 

[kae0r]

Ah, thanks for your welcome.

I found that my long IF statements that derived the figures had a space in.

What a plumb, I didn't think there was anything wrong with my code.

tut.

Thanks for your help.