Partially working VLOOKUP derived from INDIRECT/CONCATENATE/& ?

kae0r

New Member
Joined
Nov 2, 2011
Messages
7
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:

Code:
=I16&O38&O44
or
Code:
=CONCATENATE(I16&O38&O44)
or
Code:
=(I16&O38&O44)
or
Code:
=N13

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

I then VLOOKUP the string using this VLOOKUP:

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

I've also tried:
Code:
=VLOOKUP(CONCATENATE(I16&O38&O44),S10:T129,2,FALSE)

The vlookup tables looks like this:

Code:
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. :eek:

Cheers,
Kyle

Any ideas?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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

New Member
Joined
Nov 2, 2011
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top