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

Using Vlookup with merged cells...
 

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?

--

Regnab Wrote:
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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=539128

Using Vlookup with merged cells...
 

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

Using Vlookup with merged cells...
 

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" wrote in message
ups.com...
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

Using Vlookup with merged cells...
 

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

--

Peo Sjoblom Wrote:
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" wrote in message
ups.com...
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



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=539128

Using Vlookup with merged cells...
 

-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

Code:
--------------------

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' stop events
Application.EnableEvents = False
On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Dim iRow As Integer, iLastRow As Integer
iRow = Target.Row
iLastRow = Range("A65536").End(xlUp).Row
ActiveSheet.AutoFilterMode = False
Range("A1:A" & iLastRow).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Range("a" & iRow).Value
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub

--------------------

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

--

Bryan Hessey Wrote:
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)?

--


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=539128

Using Vlookup with merged cells...
 

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