# Partially working VLOOKUP derived from INDIRECT/CONCATENATE/& ?

#### kae0r

##### New Member
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.

Cheers,
Kyle

Any ideas?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.

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.

Replies
1
Views
314
Replies
5
Views
266
Replies
7
Views
492
Replies
0
Views
1K
Replies
3
Views
282

1,203,762
Messages
6,057,219
Members
444,915
Latest member
getrdon24

### 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?

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