VLookup or other?
 

I'm not sure if VLookup is the process I'm looking for, but if it is, I
can't figure out a way to get it to work. My scenario is this:

I have information listed in 3 columns: Name, Address, Phone. I want to
use a UserForm to search these columns and display the info.

If a user searches via column A (Name) and it matches, the results show the
address and phone. If the user searches via column B (Address) and it
matches, the results show Name and Phone, as well. Same goes if the user
searches column C for Phone.

Can someone help me or direct me in the right direction to accomplish this
task? Any help would be truly appreciated.

Mark

VLookup or other?
 

How about an alternative?

Select those three columns and do:
Data|Filter|autofilter

Then you can use those arrows to show the rows that meet your criteria.

Data|Filter|show all

To see everything.

Bruise wrote:

I'm not sure if VLookup is the process I'm looking for, but if it is, I
can't figure out a way to get it to work. My scenario is this:

I have information listed in 3 columns: Name, Address, Phone. I want to
use a UserForm to search these columns and display the info.

If a user searches via column A (Name) and it matches, the results show the
address and phone. If the user searches via column B (Address) and it
matches, the results show Name and Phone, as well. Same goes if the user
searches column C for Phone.

Can someone help me or direct me in the right direction to accomplish this
task? Any help would be truly appreciated.

Mark

--

Dave Peterson

VLookup or other?
 

You could use the find function...

dim wks as worksheet
dim rngToSearch as range
dim rngFound as range

set wks = sheets("Sheet1")
set rngtosearch = wks.range("A:C")

set rngfound = rngtosearch.find(What:="Bruise", LookAt:=xlPart)
if rngfound is nothing then
msgbox "Not Found"
else
msgbox wks.cells(rngfound.row, 1)
msgbox wks.cells(rngfound.row, 2)
msgbox wks.cells(rngfound.row, 3)
end if
--
HTH...

Jim Thomlinson


"Bruise" wrote:

I'm not sure if VLookup is the process I'm looking for, but if it is, I
can't figure out a way to get it to work. My scenario is this:

I have information listed in 3 columns: Name, Address, Phone. I want to
use a UserForm to search these columns and display the info.

If a user searches via column A (Name) and it matches, the results show the
address and phone. If the user searches via column B (Address) and it
matches, the results show Name and Phone, as well. Same goes if the user
searches column C for Phone.

Can someone help me or direct me in the right direction to accomplish this
task? Any help would be truly appreciated.

Mark

VLookup or other?
 

Hi Mark

It is possible using vlookup. But its bit complicated.. . and if you
want the answer here it is.

Let's assume that ur table is in sheet1 in first 3 columns. You need
to add two more columns to this; Just copy Columns A and B to columns D
and E. This is to enable all 3 vlookups.
In sheet2 say you want to enter of your search items in cells a2, b2,
and c2 for Name, Address, and Phone respectively. Enter the following
formulas in respective cells:

A3
=IF(ISNA(VLOOKUP(A2,Sheet1!A:E,1,0)),IF(ISNA(VLOOK UP(B2,Sheet1!B:E,3,0)),IF(ISNA(VLOOKUP(C2,Sheet1!C :E,2,0)),"Not
Found",VLOOKUP(C2,Sheet1!C:E,2,0)),(VLOOKUP(B2,She et1!B:E,3,0))),(VLOOKUP(A2,Sheet1!A:E,1,0)))

B3
=IF(ISNA(VLOOKUP(A2,Sheet1!A:E,2,0)),IF(ISNA(VLOOK UP(B2,Sheet1!B:E,1,0)),IF(ISNA(VLOOKUP(C2,Sheet1!C :E,3,0)),"Not
Found",VLOOKUP(C2,Sheet1!C:E,3,0)),(VLOOKUP(B2,She et1!B:E,1,0))),(VLOOKUP(A2,Sheet1!A:E,2,0)))

C3
=IF(ISNA(VLOOKUP(A2,Sheet1!A:E,3,0)),IF(ISNA(VLOOK UP(B2,Sheet1!B:E,2,0)),IF(ISNA(VLOOKUP(C2,Sheet1!C :E,1,0)),"Not
Found",VLOOKUP(C2,Sheet1!C:E,1,0)),(VLOOKUP(B2,She et1!B:E,2,0))),(VLOOKUP(A2,Sheet1!A:E,3,0)))

Now enter your search item in cell a2, b2, c2 as the case may be and if
there is a matching result it will come up in cells a3, b3 and c3.

Cells A1:C1 are for headings.

Thanks
Karthik Bhat

VLookup or other?
 

Thank you, Karthik. This worked perfectly. I was hoping to use it in a
UserForm setting with VBA code, but this worked just as well.

Thank you for the assistance.

Mark


"Karthik Bhat - Bangalore" wrote in message
ups.com...
Hi Mark

It is possible using vlookup. But its bit complicated.. . and if you
want the answer here it is.

Let's assume that ur table is in sheet1 in first 3 columns. You need
to add two more columns to this; Just copy Columns A and B to columns D
and E. This is to enable all 3 vlookups.
In sheet2 say you want to enter of your search items in cells a2, b2,
and c2 for Name, Address, and Phone respectively. Enter the following
formulas in respective cells:

A3
=IF(ISNA(VLOOKUP(A2,Sheet1!A:E,1,0)),IF(ISNA(VLOOK UP(B2,Sheet1!B:E,3,0)),IF(ISNA(VLOOKUP(C2,Sheet1!C :E,2,0)),"Not
Found",VLOOKUP(C2,Sheet1!C:E,2,0)),(VLOOKUP(B2,She et1!B:E,3,0))),(VLOOKUP(A2,Sheet1!A:E,1,0)))

B3
=IF(ISNA(VLOOKUP(A2,Sheet1!A:E,2,0)),IF(ISNA(VLOOK UP(B2,Sheet1!B:E,1,0)),IF(ISNA(VLOOKUP(C2,Sheet1!C :E,3,0)),"Not
Found",VLOOKUP(C2,Sheet1!C:E,3,0)),(VLOOKUP(B2,She et1!B:E,1,0))),(VLOOKUP(A2,Sheet1!A:E,2,0)))

C3
=IF(ISNA(VLOOKUP(A2,Sheet1!A:E,3,0)),IF(ISNA(VLOOK UP(B2,Sheet1!B:E,2,0)),IF(ISNA(VLOOKUP(C2,Sheet1!C :E,1,0)),"Not
Found",VLOOKUP(C2,Sheet1!C:E,1,0)),(VLOOKUP(B2,She et1!B:E,2,0))),(VLOOKUP(A2,Sheet1!A:E,3,0)))

Now enter your search item in cell a2, b2, c2 as the case may be and if
there is a matching result it will come up in cells a3, b3 and c3.

Cells A1:C1 are for headings.

Thanks
Karthik Bhat