how to vlookup returning a concatenation of 2 cells

[Loz]

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

 

[Alan Beban]

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

Alan Beban

 

[Cecilkumara Fernando]

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

 

[Patrick Molloy]

this does not work for me
Excel XP

Patrick Molloy

 

[Random]

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,2,FALSE)&IF(ISERROR(VLOOKUP($I$10,B,3,FALSE)),"",VLOOKUP($I$10,B,3,FALSE))


Hope this helps.

 

[Alan Beban]

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

Alan Beban

 

[Alan Beban]

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]

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.

 

[Alan Beban]

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

 

[Loz]

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

 

[Alan Beban]

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

 

[Alan Beban]

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