Ooh ooh, me sir!

Use =VLOOKUP.

It'll be along the lines of =VLOOKUP(sheet2!A2, $sheet1!$a$2:$sheet1$!a$1001,)

Your lookup range has to be ordered sequentially I think and there's a 0/1 option after the second comma in the formula and I forget which one.

The dollars fix your reference range so it doesn't try and increment that when you copy down.

I think it returns a 1 if it's found. You then filter that column by 1, voila, your list of records in the other list.

Get it?

Posted By: trafford_canary on July 29th 2014 at 16:15:00

Message Thread

Excel help, please... (General Chat) - Yellalee, Jul 29, 16:03:08
- You can also use the count function... (General Chat) - wobbly, Jul 29, 17:39:56
  - Sorry =countif(range of large list,cell to compare) (n/m) (General Chat) - wobbly, Jul 29, 17:41:29
- ULookup.... (General Chat) - Pixelman, Jul 29, 17:26:05
- did you try ....errrr...smelling up....the.....USB (General Chat) - usacanary, Jul 29, 17:02:33
- v look up. easy (n/m) (General Chat) - robotbear, Jul 29, 16:42:37
  - agree (n/m) (General Chat) - essexcanaryOTBC, Jul 29, 16:44:34
    - welcome back essex (n/m) (General Chat) - robotbear, Jul 29, 17:13:37
- I'd use different coloured text highlighters (n/m) (General Chat) - CB41, Jul 29, 16:32:54
  - and tipex for mistakes? (n/m) (General Chat) - essexcanaryOTBC, Jul 29, 16:44:44
    - Defotably (n/m) (General Chat) - CB41, Jul 29, 16:48:28
- Ooh ooh, me sir! (General Chat) - trafford_canary, Jul 29, 16:15:00
  - Cheers. Match has done the job I needed doing (n/m) (General Chat) - Yellalee, Jul 29, 16:21:16
    - I always forget MATCH! (n/m) (General Chat) - trafford_canary, Jul 29, 16:26:41
- Can do it with a VLOOKUP surely? (n/m) (General Chat) - JD3, Jul 29, 16:07:15
- I say, numbers, I mean alphanumeric ids (n/m) (General Chat) - Yellalee, Jul 29, 16:04:46
  - use the vlookup function.... (n/m) (General Chat) - Vivid Data Shark, Jul 29, 16:07:39
    - I'm trying to... (General Chat) - Yellalee, Jul 29, 16:08:27
      - =vlookup(A1,B2:B10,1,0) (General Chat) - Vivid Data Shark, Jul 29, 16:11:14
        
        You'll need to look the range down by doing the following (General Chat) - CityForLife, Jul 29, 16:44:47
        
        *lock, not look (n/m) (General Chat) - CityForLife, Jul 29, 16:45:22
        
        Cheers (n/m) (General Chat) - Yellalee, Jul 29, 16:20:40
      - Are they both just lists or is there data in a table next to them? (n/m) (General Chat) - JD3, Jul 29, 16:09:43
        
        I'd probably use match (General Chat) - watfordcanary, Jul 29, 16:12:38
        
        That sounds promising... (n/m) (General Chat) - Yellalee, Jul 29, 16:14:35
        
        Bingo! (General Chat) - Yellalee, Jul 29, 16:18:34
        
        Vlookup Example (General Chat) - essexcanaryOTBC, Jul 29, 16:17:50
        
        Cheers (n/m) (General Chat) - Yellalee, Jul 29, 16:20:11
        
        Similar to VLOOKUP but will return a '1' rather than the value. (General Chat) - JD3, Jul 29, 16:15:43
        
        Cheers (n/m) (General Chat) - Yellalee, Jul 29, 16:20:22
        
        to return a value, you use the match in an INDEX (General Chat) - watfordcanary, Jul 29, 16:20:09
        
        First one is a huge list, including other data items (General Chat) - Yellalee, Jul 29, 16:11:35

Reply to Message

In order to add a post to the WotB Message Board you must be a registered WotB user.

If you are not yet registered then please visit the registration page. You should ensure that their browser is setup to accept cookies.