Using Vlookup with merged cells...

In column A I have group titles. In column B I have the individual
elements. So for example, the location in ColA is "Italy", and then the
company names located in Italy are in ColB. I have merged the rows in
Col A so that if I hide some but not all the rows corresponding to the
companies, the location ("Italy") appears for those still showing.

I would like to be able to use VLookup so I could type in a company
name and it would return the location. However, because it is a merged
cell, it returns "0". Anyone got any stunning ideas how I can do this
or will I need to unmerge in order to get a result??

Cheers

Reg

Not sure about your stup, are you typing INTO a merged cell, or looking up a table of merged cells?
Valid is

=vlookup(mergedcell,table,2,false)

=vlookup(A1&B1,table,2,false)

does this help?

--

Originally Posted by Regnab

In column A I have group titles. In column B I have the individual
elements. So for example, the location in ColA is "Italy", and then the
company names located in Italy are in ColB. I have merged the rows in
Col A so that if I hide some but not all the rows corresponding to the
companies, the location ("Italy") appears for those still showing.

I would like to be able to use VLookup so I could type in a company
name and it would return the location. However, because it is a merged
cell, it returns "0". Anyone got any stunning ideas how I can do this
or will I need to unmerge in order to get a result??

Cheers

Reg

I'm looking up the value that is in a merged cell. So just say there
are 3 companies in Italy - ComA, ComB and ComC in B1, B2 and B3. A1:A3
has been merged to display "Italy". I want to use VLookup so I can
enter ComA and it will display "Italy". Starting to think it may not be
possible with a merged cell...

Thanks for your help,

Reg

No because merged A1:A3 = A1, there is no A2 or A3 with any value and all
references to them will always return a zero, however a vlookup looks up in
the leftmost column and returns its value from indexed columns to the right
so even if it would be possible to use merged cells it wouldn't work

You can use index(A2:A5,match(lookup_value,B2:B5,0)) but not with merged
cells in A

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Regnab" <[email protected]> wrote in message
news:[email protected]...
> I'm looking up the value that is in a merged cell. So just say there
> are 3 companies in Italy - ComA, ComB and ComC in B1, B2 and B3. A1:A3
> has been merged to display "Italy". I want to use VLookup so I can
> enter ComA and it will display "Italy". Starting to think it may not be
> possible with a merged cell...
>
> Thanks for your help,
>
> Reg
>

I think it would only be possible with VB code, with a Worksheet change event on column B (or the pressing of a button) to trigger a Filter parameter being set for column A to the value in column A of the row concerned.

Does that sound like what you need. ie, that a change to an item in column B (ComA) causes a filter to be set on the value in column A (Italy)?

--

Originally Posted by Peo Sjoblom

No because merged A1:A3 = A1, there is no A2 or A3 with any value and all
references to them will always return a zero, however a vlookup looks up in
the leftmost column and returns its value from indexed columns to the right
so even if it would be possible to use merged cells it wouldn't work

You can use index(A2:A5,match(lookup_value,B2:B5,0)) but not with merged
cells in A

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Regnab" <[email protected]> wrote in message
news:[email protected]...
> I'm looking up the value that is in a merged cell. So just say there
> are 3 companies in Italy - ComA, ComB and ComC in B1, B2 and B3. A1:A3
> has been merged to display "Italy". I want to use VLookup so I can
> enter ComA and it will display "Italy". Starting to think it may not be
> possible with a merged cell...
>
> Thanks for your help,
>
> Reg
>

note, test this on a spare copy of your workbook!

to test that, - on the required sheet, - rightmouse the tab, and select View Code, - then copy

Please Login or Register  to view this content.

into there, and then change an item in column B

This assumes that you have headers on row 1, "Italy" etc in column A, and "ComA" etc in column B

HTH

--

Originally Posted by Bryan Hessey

I think it would only be possible with VB code, with a Worksheet change event on column B (or the pressing of a button) to trigger a Filter parameter being set for column A to the value in column A of the row concerned.

Does that sound like what you need. ie, that a change to an item in column B (ComA) causes a filter to be set on the value in column A (Italy)?

--

Thanks for all the input guys.

Like I said, the main reason I wanted a merged cell was so if I hid
some cells, "Italy" would always be displayed. The solution I came up
with was to unmerge the cells, put 'Italy' in each cell, but then fit a
text box over the top of the 3 cells with "Italy" displayed. So it
looked merged, and when a cell was hidden the text box auto shrunk, but
the vlookup still worked. A little time consuming but not a bad
solution I thought....

Cheers

Reg

Originally Posted by Regnab

Thanks for all the input guys.

Like I said, the main reason I wanted a merged cell was so if I hid
some cells, "Italy" would always be displayed. The solution I came up
with was to unmerge the cells, put 'Italy' in each cell, but then fit a
text box over the top of the 3 cells with "Italy" displayed. So it
looked merged, and when a cell was hidden the text box auto shrunk, but
the vlookup still worked. A little time consuming but not a bad
solution I thought....

Cheers

Reg

Try this solution..
http://www.ozgrid.com/forum/showthread.php?t=177110