Concatenate Vlookup

Adam101

New Member
Joined
Feb 14, 2010
Messages
5
Hey forum goers,

Im having a problem with my vlookup formula based on two criteria. The information is stored on the data tab and the criteria are in columns A and B. ATM ive concatenated these two criteria in column C and use this as the lookup criteria. No two combinations are the same.

On the blend tab i have validation lists (lists stored on the lists tab), with the second list being depended on what is selected in the first list. I then use the formula:
Code:
=IF(ISNUMBER(MATCH(BLEND!$C3&"-@-"&BLEND!$D3,INDEX(DATA!$C$2:$AR$148,0,1))),VLOOKUP(BLEND!$C3&"-@-"&BLEND!$D3,DATA!$C$2:$AS$148,43,0),0)

It works fine except on two combinations Bayswater1-@-12 and Bayswater1-@-14-16. I tried changing the concatenate and renaming the criteria and checking the lists etc but i cannot work out why these will not work.

ive made a copy of the file as i thought it would be simplier to just have a look at it but i cant find the attach file option. And i have uploaded to a site hope you guys dont think its a virus or something. If there is a reputable place i can upload it that you would be comfortable downloading it from, please let me know.

Anyway the link is http://www.mediafire.com/file/zzzmowrircz/Database.xls

and if you have any ideas on how to fix it please let me know.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
you might want to add a ' in front of all your list values to force them into text. Without spending too much time looking at this i can only guess that it has to do with cell format.

After changing Lists!M6 to '12, it seems to make the Vlookup portion work.

Thats a start i suppose

Good luck.
 
Last edited:
Upvote 0
why are you putting the isnumber in front?

why not just put a isna trap and set it to 0 when your vlookup comes back #NA?

for example,

in cell F3:

=IF(ISNA(VLOOKUP(BLEND!$C3&"-@-"&BLEND!$D3,DATA!$C$2:$AS$148,43,0)),0,VLOOKUP(BLEND!$C3&"-@-"&BLEND!$D3,DATA!$C$2:$AS$148,43,0))

This worked for me after i put the ' in front of the list items
 
Last edited:
Upvote 0
1] It is better to use ISNUMBER(MATCH(…. In stead of ISNA(VOOKUP…., in avoid the double using VLOOKUP finction

2] The formula In F3 :

=IF(ISNUMBER(MATCH($C3&"-@-"&$D3,DATA!$C$2:$C$148,0)),VLOOKUP($C3&"-@-"&$D3,DATA!$C$2:$AS$148,43,0))

or

=IF(ISNUMBER(MATCH($C3&"-@-"&$D3,DATA!$C$2:$C$148,0)),VLOOKUP($C3&"-@-"&$D3,DATA!$C$2:$AS$148,43,0),"")

Regards
Bosco
 
Upvote 0
Ahh i knew it be something relatively simple, made the changes and worked a treat.

Thank you very much!
 
Upvote 0
Why the Bayswater1-@-12 doesn't work?

Please refer to #2 pdpbeethoven mentioned :

1] In Sheet Lists, cell M6, added a ' in front of 12, and became '12

2] In Sheet BLEND, entered the formula from F3 to F12

3] In Sheet BLEND, cell D3, re-select 12 from the dropdown list

Regards
Bosco
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top