0

Using excel 2007, I have 2 worksheets.

The first one is 1 column of about 600 numbers/IDs.

The 2 worksheet is about 30 columns for about 5000 IDs.

Im basically trying to filter out the 600 IDs listed in to first spreadsheet from the 5000 in the second spreadsheet.

I have tried multiple Vlookups similar to this.

=VLOOKUP(B1,'[ids.xls]Sheet1'!$A:$A,1,FALSE)

Where B1 is the first ID on the second spreadsheet, ids.xls contains the list of IDs im checking against and have selected all rows. then I have 1, to show the value in the 1st column for that id, the FALSE to only return if the value is exact.

I then copied this formula through the whole column, where B1, would be updated to B2, B3 etc.

But this isn't working, instead of returning the ID when it matches, the formula is returning NA, even though there is a value available.

Why is this not working? What formula can I use to check if a value matches a value from a list of values?

Thanks

Improve this question
  • Is ids.xls open when you do this? #N/A is not found, not formula error. That or you're matching one format type to another format type, so it can't find it. – Raystafarian Oct 31 '14 at 9:40
  • If there is just one column on the first spreadsheet, use Match instead of vlookup. If the list in the first worksheet is not sorted in ascending order, use exact match (match-type: 0). – fixer1234 Oct 31 '14 at 9:49
1

It is hard to tell without seeing the actual data, but what I'd reccommend to check is if the format of the given cells where the IDs are stored are indentical in the two sheets.

Improve this answer
  • Thank you, the second spreadsheet had the values at text,the first as numbers. – Sam Healey Oct 31 '14 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.