Sorry all.

Here’s my issue: I’ve got a biggish spreadsheet with about 32 columns, thousands of rows. I have a need to get some data from Column B- but only if it corresponds to an item in Column Q. (B= Postcodes, Q=Telephone numbers.)So, I only want to get the postcodes from the sheet where the telephone number= a certain value.

I’ve created a tab on the workbook that has the telephone numbers I’m interested in (400 or so) listed. I’ve been experimenting with custom lists, but it doesn’t seem to work. How do I filter this sheet to only show the relevant rows?

Or is this a job for the dreaded vlookup?

(edited)

What’s the identifier on the post codes? What is the feature that you’re looking for. An ‘if’ formula may be better than a vlookup.

If you’ve filtered on Q already, then click on the filter on B and it’ll show the values for the 400 – you can then go to “text filters” and “contains” or “begins with” to filter the subset.

Hard to know without knowing exactly what you’re after.

Vlookup sounds like the thing! Get learning. Just a tip, you’ll need to alter your table so that the telephone numbers are the first column.

EDIT: Actually, you could also try pivot tables. They would allow you to group all postcodes against a particular telephone number. Still sounds like you need a VLookup though.

You could use as VLookup and filters but it might be better to use Excel’s database functions they are a bit harder to use but very useful when you know your way around them.

Hard to know without knowing exactly what you’re after

+1

If you’ve got the phone number on a worksheet called ‘Phone’ and the rest of the data is on a sheet called ‘Data’ then you could do something like

=IF(ISERROR(VLOOKUP(Data!Q2,Phone!A:A,1,FALSE)),”Ignore”,IF(ISNUMBER(SEARCH(“SW1″,Data!B2)),”Use”,”Ignore”))

So if the phone number is in your list in Phone and the postcode contains SW1 then it will return “Use”. If it doesn’t meet those two criteria it will return “Ignore” and you can then filter to just show the “Use” ones.

Vlookup sounds like the thing! Get learning. Just a tip, you’ll need to alter your table so that the telephone numbers are the first column.

Not if you combine Match and Index, that lets you do the same thing as a Vlookup but looking to the right rather than the left of the lookup column.

Not if you combine Match and Index, that lets you do the same thing as a Vlookup but looking to the right rather than the left of the lookup column.

True but in light of this comment…

Or is this a job for the dreaded vlookup?

Sometimes simpler is better.

Sorry, thought I’d explained this properly, but haven’t:

I have 2 columns of data= B (postcodes), Q, (telephone numbers)

I only want to show certain postcodes, based on a subset of Q. I’ve created a separate tab with the subset of Q on it.

So, if a telephone number in Q is (for example) 01888888888, show the corresponding postcode in column B- and iterate 400 times.

Does that clarify things?

Right, let’s say your data is in a worksheet called Data. Your postcodes are in column B and phone numbers are in column Q.

In your new tab cells A1 to A400 (or however many) are your phone numbers that you want to look up. In cell B1, type the following:

=INDIRECT(“Data!B”&MATCH(A1,Data!$Q:$Q,0))

You can then drag-fill this formula down to B400, or however many phone numbers you are looking up.

What this does is looks up the phone number in cell A1 (new worksheet) in column Q of the Data worksheet. It identifies the row number for that value. It then returns the value (postcode) of the cell with that row number, in the B column.

Thanks Will- new command to me, and seems to do the trick.

(Like many I suspect, Excel use is not really meant to be part of my job and yet often seems to be the way to achieve an end. What I’m saying is: I’ve had no training! So it is mysterious to me.)

cheers!

Yeah, INDIRECT and MATCH are useful for situations where vlookups won’t work (like if the data columns are not in a convenient order).

I should probably point out that if the phone number you are looking up appears more than once in your original data, that formula will only pick up the first occurrence.

You’d probably need some VBA to get around that.

Its cool, I have no duplicates. Thanks again, I’ll experiment with these commands when I get some free time.