search

  • Thread starter Thread starter erables40
  • Start date Start date
E

erables40

I want to lookup a value in a sheet and when found give me the value
of the cell next to it. Vlookup will not work because the value looked
for is not necessarily in the same column. If I use
=VLOOKUP("beef",B:F,2,FALSE) it works great if the word beef is in
column B but if it is in A or C then I get N/A. Any other way of doing
this?
The reason is that I am doing a copy paste from a web site and looking
for specific values.
TIA
 
In your search, you're returning the value in column C. What column do you
want to return if "beef" is in column A? And, what column do you want to
return if "beef" is in column C?

Tyro
 
Hi,

First if it is in column A it is outside the range specified by your
formula! So what exactly do you want? If column B3 contains Beef do you
want it to return the C3, A3, B4, B2, C2, C4, A2 or A4? All of these cells
are "next to" B3.

For what its worth, using VLOOKUP against entire columns may be a resource
intensive approach regardless of what you are really after. Suppose your
data only extends to row 10, Excel may search all ~1,480,000 rows which is
obviously a real waste of time. (Excel 2007 - in 2003 that is only 65,536,
which is still a waste.)

What do you want Excel to do if it finds Beef in more than one cell, say
cells C4 and F102?

Thanks,
Shane
 
VLOOKUP requires that your table be 'sorted' to function correctly.
You can use TRUE or FALSE as the last parameter but usually you only
want to find an exact match so you need FALSE.

By definition you are looking in a single column and once your value
is found then VLOOKUP expects to be told how far to the right to look
for a value to return.

Dennis
 
=vlookup() doesn't need to have the table sorted.

But if the table isn't sorted by the key column and the 4th argument is True,
then the results may not be what the user really wants.

But if you're searching for an exact match (4th argument is False), then the
sort order isn't important. The row with the first match (if it exists) will
have its values returned.
 
=vlookup() doesn't need to have the table sorted.

But if the table isn't sorted by the key column and the 4th argument is True,
then the results may not be what the user really wants.

But if you're searching for an exact match (4th argument is False), then the
sort order isn't important. The row with the first match (if it exists) will
have its values returned.
Click to expand...

Ok thanks for the replies but the problem is that the word beef is not
necessarily in the same column all the time. By using Vlookup the word
beef has to be in the first column of my array right? I am trying to
do a copy paste of text from a website and if the category is ex beef
then a need the info to to cell of the right. But by doind copy paste
I do not know where the text will fall.
Let me be more specific when I buy wine I could put the receipt code
in this site
http://www.saq.com/webapp/wcs/store...Id=60921&parent_category_rn=&shouldCachePage=
Then I would like to have the info in excel so if I want to do my own
filtering on example pairing a bottle with food I can do it with my
personal wine collection.
If I want to have Pasta tonight I need a way to match it to a specific
bottle(this part I can do) but it's getting all that text in an
organized way that I need help on.
So if I can extract the info. And regroup it I can do my own rows of
info. Complicated I guess. Almost seems I had too much wine already
 
I can think of a couple of approaches. But they all would depend on what you
want returned.

1. What version of excel are you using?

2. Is a VBA solution ok?

3. Is the key word in a cell by itself?

4. What column should be returned with the =vlookup()

5. How many columns are possible in the table to be searched?
 
I can think of a couple of approaches. But they all would depend on what you
want returned.

1. What version of excel are you using?

2. Is a VBA solution ok?

3. Is the key word in a cell by itself?

4. What column should be returned with the =vlookup()

5. How many columns are possible in the table to be searched?





(e-mail address removed) wrote:
Click to expand...

Excel 2007
Vba would be fine
Yes the keyword in a cell by itself( you can do a copy paste from the
link in my previous post) If I would do a copy past all info would be
in the same cell then I wouldn't have a problem.

The cell required would be the one to the right of the word. So If the
search is for the word looked for is is C3 then i would need the info
in D3, if in A5 then I would need B5

The maximum colums should be no more than 10.

Thanks in advance for your help.
 
First, this won't work in versions of excel before xl2002--just in case you
think you may use it there. (You can't use .find in a UDF called from a
worksheet cell until xl2002.)



Option Explicit
Function myLookup(KeyValue As String, LookupRng As Range) As Variant

Dim FoundCell As Range

With LookupRng
Set FoundCell = .Cells.Find(what:=KeyValue, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
myLookup = "Not Found!" 'anything you want
Else
myLookup = FoundCell.Offset(0, 1).Value
End If
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=myLookup(a1,sheet99!a:j)
or
=myLookup(a1,sheet99!a1:x999)
 
First, this won't work in versions of excel before xl2002--just in case you
think you may use it there. (You can't use .find in a UDF called from a
worksheet cell until xl2002.)

Option Explicit
Function myLookup(KeyValue As String, LookupRng As Range) As Variant

Dim FoundCell As Range

With LookupRng
Set FoundCell = .Cells.Find(what:=KeyValue, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
myLookup = "Not Found!" 'anything you want
Else
myLookup = FoundCell.Offset(0, 1).Value
End If
End Function

If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=myLookup(a1,sheet99!a:j)
or
=myLookup(a1,sheet99!a1:x999)
Click to expand...

Thanks works great!!!
 
Back
Top