Re: New to using VLOOKUP Function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: john.bedford3 (john.bedford3_at_@ntlworld.com)
Date: 03/18/05 

Date: Fri, 18 Mar 2005 23:28:20 GMT

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? 

-- 
John
Delete extra @ to reply
"Trevor Shuttleworth" <Trevor@Shucks.demon.co.uk> wrote in message
news:uqh$$3ALFHA.2852@TK2MSFTNGP14.phx.gbl...
> 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
>
>
> "john.bedford3" <john.bedford3@@ntlworld.com> wrote in message
> news:sxI_d.4395$MO6.3501@newsfe2-gui.ntli.net...
> > 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.
> >
> >
> > -- 
> > John
> > Delete extra @ to reply
> >
> >
>
>

Relevant Pages

  • Re: [BUG] Something goes wrong with timer statistics.
    ... into the list to avoid a race with the fastpath lookup. ... can still point into the entries array. ... initialization of a new entry is finished upon insertion of that entry. ...
    (Linux-Kernel)
  • Re: [BUG] Something goes wrong with timer statistics.
    ... into the list to avoid a race with the fastpath lookup. ... can still point into the entries array. ... initialization of a new entry is finished upon insertion of that entry. ...
    (Linux-Kernel)
  • Re: RegExp split for Spell Check
    ... 1,000 entries which is 999 more than it has to look up. ... the trivial matter of reporting success in an appropriate form. ... the last lookup, as a check. ... The lookup time is pretty standard and testing with a 215,000 entry dictionary bears that out for me. ...
    (comp.lang.javascript)
  • Re: mailc entry in /etc/hosts
    ... I have following entries in a customer /etc/hosts file: ... mailc aaa.com hostname ... DW entry in /etc/sendmail.cf references the hostname given in the ... Lookup of the address for 'mailc' will always give 10.8.197.6. ...
    (comp.mail.sendmail)
  • Re: Newbie question. Keyboard shortcuts?
    ... you could create an AutoText entry of the name Trevor and assign it to ... Word MVP FAQ site: http://word.mvps.org ... > keyboard shortcut to automatically enter a word at the cursor position. ...
    (microsoft.public.word.newusers)