how to vlookup returning a concatenation of 2 cells
 

Hi,

I was hoping someone could help me with a problem I have
please. I know (hope) it isn't too complicated, but I just
can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10.
I need code to read H10 and find it in Col B and return
the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz

how to vlookup returning a concatenation of 2 cells
 

=VLOOKUP(H10,B:D,{2,3}) array entered into a 2-column row

Alan Beban

Loz wrote:
Hi,

I was hoping someone could help me with a problem I have
please. I know (hope) it isn't too complicated, but I just
can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10.
I need code to read H10 and find it in Col B and return
the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz

how to vlookup returning a concatenation of 2 cells
 

Loz,
try this (adjust $B$1:$D$18 to suit)

Sub test()
For i = 8 To 9
If Not Cells(10, i).Value = IsNothing Then
If Not IsError(Evaluate("VLOOKUP(" _
& Cells(10, i).Address & ",$B$1:$D$18,2,0)")) Then
Cells(11, i).Value = Evaluate _
("VLOOKUP(" & Cells(10, i).Address & ",$B$1:$D$18,2,0)") & _
Evaluate("VLOOKUP(" & Cells(10, i).Address & ",$B$1:$D$18,3,0)")
End If
End If
Next i
End Sub

HTH
Cecil

"Loz" wrote in message
...
Hi,

I was hoping someone could help me with a problem I have
please. I know (hope) it isn't too complicated, but I just
can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10.
I need code to read H10 and find it in Col B and return
the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz

how to vlookup returning a concatenation of 2 cells
 

this does not work for me :(
Excel XP

Patrick Molloy
-----Original Message-----
=VLOOKUP(H10,B:D,{2,3}) array entered into a 2-column row

Alan Beban

Loz wrote:
Hi,

I was hoping someone could help me with a problem I
have
please. I know (hope) it isn't too complicated, but I
just
can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10.
I need code to read H10 and find it in Col B and
return
the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz

.

how to vlookup returning a concatenation of 2 cells
 

I could not get it to work in Excel 2000 either... Tried entering it
as an array, still didn't work.. I did, however get this to work for
your needs.....

=VLOOKUP($H$10,B:D,2,FALSE)&IF(ISERROR(VLOOKUP($I$ 10,B:D,3,FALSE)),"",VLOOKUP($I$10,B:D,3,FALSE))


Hope this helps.



On Thu, 07 Aug 2003 01:02:01 -0700, Alan Beban
wrote:

It works in xl2000; shall I email you the file?

Alan Beban

Patrick Molloy wrote:
this does not work for me :(
Excel XP

Patrick Molloy

-----Original Message-----
=VLOOKUP(H10,B:D,{2,3}) array entered into a 2-column row

Alan Beban

Loz wrote:

Hi,

I was hoping someone could help me with a problem I

have

please. I know (hope) it isn't too complicated, but I

just

can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10.
I need code to read H10 and find it in Col B and

return

the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz

.

how to vlookup returning a concatenation of 2 cells
 

If you send me your email address I'll be happy to send you the xl2000
file in which it works.

Alan Beban

Random wrote:
I could not get it to work in Excel 2000 either... Tried entering it
as an array, still didn't work.. I did, however get this to work for
your needs.....

=VLOOKUP($H$10,B:D,2,FALSE)&IF(ISERROR(VLOOKUP($I$ 10,B:D,3,FALSE)),"",VLOOKUP($I$10,B:D,3,FALSE))


Hope this helps.



On Thu, 07 Aug 2003 01:02:01 -0700, Alan Beban
wrote:


It works in xl2000; shall I email you the file?

Alan Beban

Patrick Molloy wrote:

this does not work for me :(
Excel XP

Patrick Molloy


-----Original Message-----
=VLOOKUP(H10,B:D,{2,3}) array entered into a 2-column row

Alan Beban

Loz wrote:


Hi,

I was hoping someone could help me with a problem I

have


please. I know (hope) it isn't too complicated, but I

just


can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10.
I need code to read H10 and find it in Col B and

return


the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz

.

how to vlookup returning a concatenation of 2 cells
 

After corresponding with Mark Bigelow, and reviewing this thread,
particularly
Random's last post below, it finally becomes clear--the OP's original
specification is not clear. I posted a "solution" that returned to two
cells
the two values from the OP's Columns C and D for the value in the OP's
Cell H10.

Random posted a formula that returns to a single cell the concatenated
values from the OP's Column C for the OP's Cell H10 and from the OP's
Column D for the OP's Cell I10.

From rereading the original post, it seenms the OP wanted two values
returned corresponding to the OP's Cell H10, and an additional two
values corresponding to the OP's Cell I10.

Perhaps the OP could clarify what's supposed to be returned to where,
concatenated with what :_)

Alan Beban

Random wrote:

I could not get it to work in Excel 2000 either... Tried entering it
as an array, still didn't work.. I did, however get this to work for
your needs.....

=VLOOKUP($H$10,B:D,2,FALSE)&IF(ISERROR(VLOOKUP($I$ 10,B:D,3,FALSE)),"",VLOOKUP($I$10,B:D,3,FALSE))



Hope this helps.



On Thu, 07 Aug 2003 01:02:01 -0700, Alan Beban
wrote:


It works in xl2000; shall I email you the file?

Alan Beban

Patrick Molloy wrote:

this does not work for me :(
Excel XP

Patrick Molloy


-----Original Message-----
=VLOOKUP(H10,B:D,{2,3}) array entered into a 2-column row

Alan Beban

Loz wrote:


Hi,

I was hoping someone could help me with a problem I


have

please. I know (hope) it isn't too complicated, but I


just

can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10. I need code to read H10 and find
it in Col B and


return

the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz


.

how to vlookup returning a concatenation of 2 cells
 

I must apologize here. I have reread Alan's post as well and found
out that it does work in Excel 2000 quite well. I am very new to the
array idea with excel and did not try to enter it into a two column
array of cells. Thanks for the insight there. I enjoy learning
something new. Can anyone recommend a site where the array functions
are explained in detail?

Thanks for your patience.


On Thu, 07 Aug 2003 13:10:50 -0700, Alan Beban
wrote:

After corresponding with Mark Bigelow, and reviewing this thread,
particularly
Random's last post below, it finally becomes clear--the OP's original
specification is not clear. I posted a "solution" that returned to two
cells
the two values from the OP's Columns C and D for the value in the OP's
Cell H10.

Random posted a formula that returns to a single cell the concatenated
values from the OP's Column C for the OP's Cell H10 and from the OP's
Column D for the OP's Cell I10.

From rereading the original post, it seenms the OP wanted two values
returned corresponding to the OP's Cell H10, and an additional two
values corresponding to the OP's Cell I10.

Perhaps the OP could clarify what's supposed to be returned to where,
concatenated with what :_)

Alan Beban

Random wrote:

I could not get it to work in Excel 2000 either... Tried entering it
as an array, still didn't work.. I did, however get this to work for
your needs.....

=VLOOKUP($H$10,B:D,2,FALSE)&IF(ISERROR(VLOOKUP($I$ 10,B:D,3,FALSE)),"",VLOOKUP($I$10,B:D,3,FALSE))



Hope this helps.



On Thu, 07 Aug 2003 01:02:01 -0700, Alan Beban
wrote:


It works in xl2000; shall I email you the file?

Alan Beban

Patrick Molloy wrote:

this does not work for me :(
Excel XP

Patrick Molloy


-----Original Message-----
=VLOOKUP(H10,B:D,{2,3}) array entered into a 2-column row

Alan Beban

Loz wrote:


Hi,

I was hoping someone could help me with a problem I


have

please. I know (hope) it isn't too complicated, but I


just

can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10. I need code to read H10 and find
it in Col B and


return

the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz


.

how to vlookup returning a concatenation of 2 cells
 

Whoa! Don't apologize yet. I appreciate your reposting to mention that
my code does what I meant it to do; but the subject line of the OP's
post did refer to concatenating something, which my "solution" doesn't
do and yours does, though it concatenates only two of the presumably 4
return values. I'm still waiting to find out just what the OP is hoping
to accomplish.

I'm sure there are several resources discussing array formulas, but a
good place to start is at Chip Pearson's site,

http://www.cpearson.com/excel/array.htm

Thanks for the kind words,
Alan Beban

Random wrote:
I must apologize here. I have reread Alan's post as well and found
out that it does work in Excel 2000 quite well. I am very new to the
array idea with excel and did not try to enter it into a two column
array of cells. Thanks for the insight there. I enjoy learning
something new. Can anyone recommend a site where the array functions
are explained in detail?

Thanks for your patience.


On Thu, 07 Aug 2003 13:10:50 -0700, Alan Beban
wrote:


After corresponding with Mark Bigelow, and reviewing this thread,
particularly
Random's last post below, it finally becomes clear--the OP's original
specification is not clear. I posted a "solution" that returned to two
cells
the two values from the OP's Columns C and D for the value in the OP's
Cell H10.

Random posted a formula that returns to a single cell the concatenated
values from the OP's Column C for the OP's Cell H10 and from the OP's
Column D for the OP's Cell I10.

From rereading the original post, it seenms the OP wanted two values
returned corresponding to the OP's Cell H10, and an additional two
values corresponding to the OP's Cell I10.

Perhaps the OP could clarify what's supposed to be returned to where,
concatenated with what :_)

Alan Beban


Random wrote:


I could not get it to work in Excel 2000 either... Tried entering it
as an array, still didn't work.. I did, however get this to work for
your needs.....

=VLOOKUP($H$10,B:D,2,FALSE)&IF(ISERROR(VLOOKUP ($I$10,B:D,3,FALSE)),"",VLOOKUP($I$10,B:D,3,FALSE) )



Hope this helps.



On Thu, 07 Aug 2003 01:02:01 -0700, Alan Beban
wrote:



It works in xl2000; shall I email you the file?

Alan Beban

Patrick Molloy wrote:


this does not work for me :(
Excel XP

Patrick Molloy



-----Original Message-----
=VLOOKUP(H10,B:D,{2,3}) array entered into a 2-column row

Alan Beban

Loz wrote:



Hi,

I was hoping someone could help me with a problem I


have


please. I know (hope) it isn't too complicated, but I


just


can't do it. My logic isn't computing. }:-s

I have 2 labels. $H$10 & $I$10. I need code to read H10 and find
it in Col B and


return


the contents of Col C (Path) & Col D (Filename).
Then, if there is a label in I10 do the same.

I look forward to any assistance
Thank you!
Loz


.

how to vlookup returning a concatenation of 2 cells
 

Hi everyone,
Sorry I didn't reply earlier (it's 10am in Sydney atm and
I've just logged on...)
This is what I was trying to achieve:

A
1 c:\files\ (DropDown1 of B3:B10)
2 (DropDown2 of B3:B10)
A B c D
3 Ibm IBM FAStT700 IBM\ ibm.xls
4 Dell EMC CX600 DEL\ dell.xls


DropDown1 puts selection into cell H10
DropDown2 puts selection into cell I10 (if one was
selected)

Then I wanted the code to find H10 in B3:B10 and open the
file Ie. IBM FAStT700 selected and then opens A1 & C3 & D3

Then IF I10 was populated with another option ie EMC CX600
it would then open that file.

I am really sorry if I was unclear (or still am) maybe I
am going about it all wrong?!?!?! I am just plodding along
and trying to achieve the best i can :-)

Thank you for your support!
I will also try the examples provided today thanks

Kind regards,
Lauren

how to vlookup returning a concatenation of 2 cells
 

Do I understand correctly that you expect to accomplish this with a Sub
procedure (a macro)? Or do you simply want, for example,
c:\files\IBM\ibm.xls to appear in a cell(e.g., H11) for later operation?
And c:\files\DEL\dell.xls to appear in, e.g., I11?

Alan Beban

Loz wrote:
Hi everyone,
Sorry I didn't reply earlier (it's 10am in Sydney atm and
I've just logged on...)
This is what I was trying to achieve:

A
1 c:\files\ (DropDown1 of B3:B10)
2 (DropDown2 of B3:B10)
A B c D
3 Ibm IBM FAStT700 IBM\ ibm.xls
4 Dell EMC CX600 DEL\ dell.xls


DropDown1 puts selection into cell H10
DropDown2 puts selection into cell I10 (if one was
selected)

Then I wanted the code to find H10 in B3:B10 and open the
file Ie. IBM FAStT700 selected and then opens A1 & C3 & D3

Then IF I10 was populated with another option ie EMC CX600
it would then open that file.

I am really sorry if I was unclear (or still am) maybe I
am going about it all wrong?!?!?! I am just plodding along
and trying to achieve the best i can :-)

Thank you for your support!
I will also try the examples provided today thanks

Kind regards,
Lauren

how to vlookup returning a concatenation of 2 cells
 

Put the following formula in Cell H11 and fill it over to I11

=$A$1&VLOOKUP(H10,$B$4:$D$10,2,FALSE)&VLOOKUP(H10, $B$4:$D$10,3,FALSE)

Then without changing the active sheet run the following procedure

Sub TestVlook()
Dim wb As Workbook, ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Workbooks.Open Filename:=Range("h11").Value
ws.Activate
On Error Resume Next
If Not Range("i11").Value = "#N/A!" Then Workbooks.Open _
Filename:=Range("i11").Value
End Sub

The above formula does not have error trapping to take care of the
situation in which H10 doesn't have in it a value that is included in B4:B10

Alan Beban

Loz wrote:
Hi everyone,
Sorry I didn't reply earlier (it's 10am in Sydney atm and
I've just logged on...)
This is what I was trying to achieve:

A
1 c:\files\ (DropDown1 of B3:B10)
2 (DropDown2 of B3:B10)
A B c D
3 Ibm IBM FAStT700 IBM\ ibm.xls
4 Dell EMC CX600 DEL\ dell.xls


DropDown1 puts selection into cell H10
DropDown2 puts selection into cell I10 (if one was
selected)

Then I wanted the code to find H10 in B3:B10 and open the
file Ie. IBM FAStT700 selected and then opens A1 & C3 & D3

Then IF I10 was populated with another option ie EMC CX600
it would then open that file.

I am really sorry if I was unclear (or still am) maybe I
am going about it all wrong?!?!?! I am just plodding along
and trying to achieve the best i can :-)

Thank you for your support!
I will also try the examples provided today thanks

Kind regards,
Lauren