New to using VLOOKUP Function

J

john.bedford3

Hi, I am new to using the VLOOKUP function and would like a bit of help.

I have created a sheet labelled 'Lookup List' and have data in the range
B4:H52 and sorted by column B

as follows.
B C D E F
G H
Species Date Place lbs ozs drms
Kilos
Barbel 25-Aug-1984 River Kennet 2 13 0.0 1.275
Chub 01-Jan-1980 Luxborough Pit 1 5 0.0 0.595
Chub 01-Mar-1981 R. Roding, Chigwell 1 4 0.0 0.566
Chub 01-Mar-1981 R. Roding, Chigwell 1 3 0.0 0.538
Chub 10-Sep-1983 R. Lea, 0 15 0.0
0.425
Bream 01-Jun-1981 Lower Lough Erne 2 12 0.0 1.247
Bream 01-Jul-1982 River Beult, Hunton 1 12 0.0 0.793

I have used the following formula to populate a list on a different sheet.

=VLOOKUP("CHUB",'Lookup List'!B4:H52,2,TRUE) amending the Col index number
for each column.

The result was as follows producing 5 entries instead of the expected 4 and
all 5 entries are the details of the last entry for Chub in the lookup list.

Date Place lbs ozs drms
Kilos
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
#N/A #N/A #N/A #N/A #N/A #N/A

I have obviously got the formula wrong or misinterpreted the way this
function works. Is there any way to get this to work? If so could you also
tell me how I can return a blank cell instead of #N/A when the result is not
TRUE?

Any help would be gratefully appreciated.
 
T

Trevor Shuttleworth

John

for the lookup, you would need something like:

=VLOOKUP("CHUB",'Lookup List'!$B$4:$H$52,2,FALSE)

However, this will always find the first entry. Usually, you use VLOOKUP to
find a unique entry in a lookup table.

Use =IF(ISNA(your_lookup),"",your_lookup) to avoid the #N/A

Regards

Trevor
 
C

CLR

A couple of things.........VLOOKUP will only find one occurance in the
database, you can't double-up with VLOOKUP's to find the others..........and
if you're looking for an exact match, you should use FALSE instead of TRUE,
(like pressing the START button to STOP <g>), and if you want a blank cell
instead of #N/A, you can wrap your VLOOKUP formula in an IF statement, like
=IF(ISNA(yourLOOKUPformula),"",yourLOOKUPformula)..........and if you assign
a RangeName to your Table, you can address it by that name instead of having
to use cell references.........=VLOOKUP(A1,MyNamedRange,2,FALSE)

hth
Vaya con Dios,
Chuck, CABGx3
 
M

Max

Perhaps you'd to try this set-up instead ..
(vlookup may not be suitable here)

In the sheet labelled: Lookup List
----------------------------
(The key column "Species" is assumed in col B,
as posted, with data from row2 down)

Using an empty col to the right, say col K

Put in K1: =TRIM(Sheet2!A1)

Put in K2:
=IF(B2="","",IF(TRIM(B2)=K$1,ROW(),""))

Copy K2 down to say, K1000
to cover the max expected data in cola B to H

In another sheet named: Sheet2
--------------------------
Let's earmark A1 for input of the "Species" that you want
Enter an input in A1: Chub (say)

Paste the same headers for cols B to H in 'Lookup List'
into A2:G2, viz.: Species Date Place lbs ozs drms Kilos

Put in A3:

=IF(ISERROR(SMALL('Lookup List'!$K:$K,ROWS($A$1:A1))),"",INDEX('Lookup
List'!B:B,MATCH(SMALL('Lookup List'!$K:$K,ROWS($A$1:A1)),'Lookup
List'!$K:$K,0)))

Copy A2 across to G2, fill down to G1000
(cover the same range as in sheet: Lookup List)

Format col B as dates

The above will auto-return the filtered rows for the species input in A1,
i.e. you'll get, for the sample source data and the sample input in A1:

Species Date Place .... etc
Chub 1-Jan-80 Luxborough Pit ... etc
Chub 1-Mar-81 R. Roding, Chigwell ... etc
Chub 1-Mar-81 R. Roding, Chigwell ... etc
Chub 10-Sep-83 R. Lea, ... etc
(Rest are blank rows [""])

If you change the input in A1 to: Bream

Species Date Place .. etc
Bream 1-Jun-81 Lower Lough Erne .. etc
Bream 1-Jul-82 River Beult, Hunton ... etc
(Rest are blank rows [""])

And so on ..
 
J

john.bedford3

Thanks Trevor that certainly finds the first item in the list but what I am
after is to find all entries that match and copy them to a new list. Should
I be using a different function?
 
M

Max

Should I be using a different function? ..
Try the set-up I've posted in the other branch ..
 
J

john.bedford3

Thanks Chuck, between what you and Trevor have explained I understand this a
lot better. VLOOKUP does not seem to be the way to go in this instance but
now I have a better understanding I can make use of this in other instances.

Thanks for all your help.
 
C

CLR

You're welcome John...........one thing you might consider, if you're
wanting to bring all the "Chub's" up at one time, for example, might be the
AutoFilter feature..........it does a good job of things like
this...........

Vaya con Dios,
Chuck, CABGx3
 
J

john.bedford3

Thanks Max, that works a treat for getting the information to the place I
want. I really appreciate the help.

I only have one problem now. On the sheet I am copying to, I had the
following formula which looks at the date column to count the number of
instances of each month regardless of year. This now returns a #VALUE!
error.

=SUMPRODUCT(--(MONTH(B9:B158)=1),--(ISNUMBER(B9:B158)))

Would I be able to solve this problem by wrapping this in an IF(ISERROR
statement?

Thanks once again.

John
Delete extra @ to reply

Max said:
Perhaps you'd to try this set-up instead ..
(vlookup may not be suitable here)

In the sheet labelled: Lookup List
----------------------------
(The key column "Species" is assumed in col B,
as posted, with data from row2 down)

Using an empty col to the right, say col K

Put in K1: =TRIM(Sheet2!A1)

Put in K2:
=IF(B2="","",IF(TRIM(B2)=K$1,ROW(),""))

Copy K2 down to say, K1000
to cover the max expected data in cola B to H

In another sheet named: Sheet2
--------------------------
Let's earmark A1 for input of the "Species" that you want
Enter an input in A1: Chub (say)

Paste the same headers for cols B to H in 'Lookup List'
into A2:G2, viz.: Species Date Place lbs ozs drms Kilos

Put in A3:

=IF(ISERROR(SMALL('Lookup List'!$K:$K,ROWS($A$1:A1))),"",INDEX('Lookup
List'!B:B,MATCH(SMALL('Lookup List'!$K:$K,ROWS($A$1:A1)),'Lookup
List'!$K:$K,0)))

Copy A2 across to G2, fill down to G1000
(cover the same range as in sheet: Lookup List)

Format col B as dates

The above will auto-return the filtered rows for the species input in A1,
i.e. you'll get, for the sample source data and the sample input in A1:

Species Date Place .... etc
Chub 1-Jan-80 Luxborough Pit ... etc
Chub 1-Mar-81 R. Roding, Chigwell ... etc
Chub 1-Mar-81 R. Roding, Chigwell ... etc
Chub 10-Sep-83 R. Lea, ... etc
(Rest are blank rows [""])

If you change the input in A1 to: Bream

Species Date Place .. etc
Bream 1-Jun-81 Lower Lough Erne .. etc
Bream 1-Jul-82 River Beult, Hunton ... etc
(Rest are blank rows [""])

And so on ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
john.bedford3 said:
Hi, I am new to using the VLOOKUP function and would like a bit of help.

I have created a sheet labelled 'Lookup List' and have data in the range
B4:H52 and sorted by column B

as follows.
B C D E F
G H
Species Date Place lbs ozs drms
Kilos
Barbel 25-Aug-1984 River Kennet 2 13 0.0 1.275
Chub 01-Jan-1980 Luxborough Pit 1 5 0.0 0.595
Chub 01-Mar-1981 R. Roding, Chigwell 1 4 0.0 0.566
Chub 01-Mar-1981 R. Roding, Chigwell 1 3 0.0 0.538
Chub 10-Sep-1983 R. Lea, 0 15 0.0
0.425
Bream 01-Jun-1981 Lower Lough Erne 2 12 0.0 1.247
Bream 01-Jul-1982 River Beult, Hunton 1 12 0.0 0.793

I have used the following formula to populate a list on a different sheet.

=VLOOKUP("CHUB",'Lookup List'!B4:H52,2,TRUE) amending the Col index number
for each column.

The result was as follows producing 5 entries instead of the expected 4 and
all 5 entries are the details of the last entry for Chub in the lookup list.

Date Place lbs ozs drms
Kilos
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
#N/A #N/A #N/A #N/A #N/A #N/A

I have obviously got the formula wrong or misinterpreted the way this
function works. Is there any way to get this to work? If so could you also
tell me how I can return a blank cell instead of #N/A when the result is not
TRUE?

Any help would be gratefully appreciated.
Click to expand...
Click to expand...
 
B

Bob Phillips

John,

Try an old-fashioned SUM(IF

=SUM(IF(ISNUMBER(B9:B158),IF(MONTH(B9:B158)=1,1,0)))

which is an array formula so commit with ctrl-shift-enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


john.bedford3 said:
Thanks Max, that works a treat for getting the information to the place I
want. I really appreciate the help.

I only have one problem now. On the sheet I am copying to, I had the
following formula which looks at the date column to count the number of
instances of each month regardless of year. This now returns a #VALUE!
error.

=SUMPRODUCT(--(MONTH(B9:B158)=1),--(ISNUMBER(B9:B158)))

Would I be able to solve this problem by wrapping this in an IF(ISERROR
statement?

Thanks once again.

John
Delete extra @ to reply

Max said:
Perhaps you'd to try this set-up instead ..
(vlookup may not be suitable here)

In the sheet labelled: Lookup List
----------------------------
(The key column "Species" is assumed in col B,
as posted, with data from row2 down)

Using an empty col to the right, say col K

Put in K1: =TRIM(Sheet2!A1)

Put in K2:
=IF(B2="","",IF(TRIM(B2)=K$1,ROW(),""))

Copy K2 down to say, K1000
to cover the max expected data in cola B to H

In another sheet named: Sheet2
--------------------------
Let's earmark A1 for input of the "Species" that you want
Enter an input in A1: Chub (say)

Paste the same headers for cols B to H in 'Lookup List'
into A2:G2, viz.: Species Date Place lbs ozs drms Kilos

Put in A3:

=IF(ISERROR(SMALL('Lookup List'!$K:$K,ROWS($A$1:A1))),"",INDEX('Lookup
List'!B:B,MATCH(SMALL('Lookup List'!$K:$K,ROWS($A$1:A1)),'Lookup
List'!$K:$K,0)))

Copy A2 across to G2, fill down to G1000
(cover the same range as in sheet: Lookup List)

Format col B as dates

The above will auto-return the filtered rows for the species input in A1,
i.e. you'll get, for the sample source data and the sample input in A1:

Species Date Place .... etc
Chub 1-Jan-80 Luxborough Pit ... etc
Chub 1-Mar-81 R. Roding, Chigwell ... etc
Chub 1-Mar-81 R. Roding, Chigwell ... etc
Chub 10-Sep-83 R. Lea, ... etc
(Rest are blank rows [""])

If you change the input in A1 to: Bream

Species Date Place .. etc
Bream 1-Jun-81 Lower Lough Erne .. etc
Bream 1-Jul-82 River Beult, Hunton ... etc
(Rest are blank rows [""])

And so on ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
john.bedford3 said:
Hi, I am new to using the VLOOKUP function and would like a bit of help.

I have created a sheet labelled 'Lookup List' and have data in the range
B4:H52 and sorted by column B

as follows.
B C D E F
G H
Species Date Place lbs ozs drms
Kilos
Barbel 25-Aug-1984 River Kennet 2 13 0.0 1.275
Chub 01-Jan-1980 Luxborough Pit 1 5 0.0 0.595
Chub 01-Mar-1981 R. Roding, Chigwell 1 4 0.0 0.566
Chub 01-Mar-1981 R. Roding, Chigwell 1 3 0.0 0.538
Chub 10-Sep-1983 R. Lea, 0 15 0.0
0.425
Bream 01-Jun-1981 Lower Lough Erne 2 12 0.0 1.247
Bream 01-Jul-1982 River Beult, Hunton 1 12 0.0 0.793

I have used the following formula to populate a list on a different sheet.

=VLOOKUP("CHUB",'Lookup List'!B4:H52,2,TRUE) amending the Col index number
for each column.

The result was as follows producing 5 entries instead of the expected
Click to expand...
4
and
all 5 entries are the details of the last entry for Chub in the lookup list.

Date Place lbs ozs drms
Kilos
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
#N/A #N/A #N/A #N/A #N/A #N/A

I have obviously got the formula wrong or misinterpreted the way this
function works. Is there any way to get this to work? If so could you also
tell me how I can return a blank cell instead of #N/A when the result
Click to expand...
is
not
TRUE?

Any help would be gratefully appreciated.
Click to expand...
Click to expand...
Click to expand...
 
J

john.bedford3

Thanks Bob. That works great.

--
John
Delete extra @ to reply

Bob Phillips said:
John,

Try an old-fashioned SUM(IF

=SUM(IF(ISNUMBER(B9:B158),IF(MONTH(B9:B158)=1,1,0)))

which is an array formula so commit with ctrl-shift-enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


john.bedford3 said:
Thanks Max, that works a treat for getting the information to the place I
want. I really appreciate the help.

I only have one problem now. On the sheet I am copying to, I had the
following formula which looks at the date column to count the number of
instances of each month regardless of year. This now returns a #VALUE!
error.

=SUMPRODUCT(--(MONTH(B9:B158)=1),--(ISNUMBER(B9:B158)))

Would I be able to solve this problem by wrapping this in an IF(ISERROR
statement?

Thanks once again.

John
Delete extra @ to reply

Max said:
Perhaps you'd to try this set-up instead ..
(vlookup may not be suitable here)

In the sheet labelled: Lookup List
----------------------------
(The key column "Species" is assumed in col B,
as posted, with data from row2 down)

Using an empty col to the right, say col K

Put in K1: =TRIM(Sheet2!A1)

Put in K2:
=IF(B2="","",IF(TRIM(B2)=K$1,ROW(),""))

Copy K2 down to say, K1000
to cover the max expected data in cola B to H

In another sheet named: Sheet2
--------------------------
Let's earmark A1 for input of the "Species" that you want
Enter an input in A1: Chub (say)

Paste the same headers for cols B to H in 'Lookup List'
into A2:G2, viz.: Species Date Place lbs ozs drms Kilos

Put in A3:

=IF(ISERROR(SMALL('Lookup List'!$K:$K,ROWS($A$1:A1))),"",INDEX('Lookup
List'!B:B,MATCH(SMALL('Lookup List'!$K:$K,ROWS($A$1:A1)),'Lookup
List'!$K:$K,0)))

Copy A2 across to G2, fill down to G1000
(cover the same range as in sheet: Lookup List)

Format col B as dates

The above will auto-return the filtered rows for the species input in A1,
i.e. you'll get, for the sample source data and the sample input in A1:

Species Date Place .... etc
Chub 1-Jan-80 Luxborough Pit ... etc
Chub 1-Mar-81 R. Roding, Chigwell ... etc
Chub 1-Mar-81 R. Roding, Chigwell ... etc
Chub 10-Sep-83 R. Lea, ... etc
(Rest are blank rows [""])

If you change the input in A1 to: Bream

Species Date Place .. etc
Bream 1-Jun-81 Lower Lough Erne .. etc
Bream 1-Jul-82 River Beult, Hunton ... etc
(Rest are blank rows [""])

And so on ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Hi, I am new to using the VLOOKUP function and would like a bit of help.

I have created a sheet labelled 'Lookup List' and have data in the range
B4:H52 and sorted by column B

as follows.
B C D E F
G H
Species Date Place lbs ozs drms
Kilos
Barbel 25-Aug-1984 River Kennet 2 13 0.0 1.275
Chub 01-Jan-1980 Luxborough Pit 1 5 0.0
0.595
Chub 01-Mar-1981 R. Roding, Chigwell 1 4 0.0 0.566
Chub 01-Mar-1981 R. Roding, Chigwell 1 3 0.0 0.538
Chub 10-Sep-1983 R. Lea, 0 15 0.0
0.425
Bream 01-Jun-1981 Lower Lough Erne 2 12 0.0 1.247
Bream 01-Jul-1982 River Beult, Hunton 1 12 0.0 0.793

I have used the following formula to populate a list on a different sheet.

=VLOOKUP("CHUB",'Lookup List'!B4:H52,2,TRUE) amending the Col index number
for each column.

The result was as follows producing 5 entries instead of the
Click to expand...
Click to expand...
expected
4 you
also
Click to expand...
result
is
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to adapt a formula? 2
VLOOKUP Problem 1
Using Vlookup function to solve the below problem 1
Windows reboots very often... 1
HP color laser $299 includes jetdirect ethernet card - extra toners NEW $20, also hp 8500 $700 8550n 1
FAT32 partition does not mount. How to recover data? 1
Driver Conflict 2
Print Spooler Stops in Windows XP 1

Top