Vlookup excel 1

[anthony777]

I have a spreadsheet that I cannot alter it has a Cust ID field in field C I want to list all my cust id that I am searching for in another spreadsheet in column a and then search the other spreadsheet called may.xls to see if any of my cust id are in spreadsheet May.xls column C cust id field. I guess in the spreadsheet I create with my cust id I would need the vlookup to bring over the cust id from the may.xls spreadsheet so I would know there is a match?

 

[xlbo]

so what's the question?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Have you considered using either MS Query or the PivotTable Wizard?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[anthony777]

the question is how do I list my cust id in one spreadsheet and do a lookup to see if they are in the other spread sheet. I need to know if the cust id I have already exist in the other spreadsheet. I was looking at the vlookup

 

[GlennUK]

I use the MATCH function, combined with ISNA to give TRUE and FALSE as to whether something exists in a another list.

For example,

=NOT(ISNA(MATCH(cust_id,other_list_ref,0)))

... this gives TRUE if the cust_id exists in the other list ( and FALSE otherwise ).

Is that what you wanted?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

How do I list my custid...

MS Query or PivotTable Wizard.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[anationalacrobat]

Vlookup is certainly feasible and you can look on other sheets.

If you are familiar with databse queries, it works like that. Use the wizard, it'll help. If on your second sheet you have the customer ID in cell A1 and want to look up related info on the first sheet, you do a vlookup(a1 with target being the full range of data you'd be looking up (just click the browse to range button and select everything), then you count the rows over to the value you want to look up. If you want zip code and that's in column E, that's 5 over. Finally, you want to make the last field false so you don't get weird returns.

Excel usually gives you a couple of different ways to accomplish something. Vlookup is one very good way. But another question you have to ask yourself is "What's housing my data?" If it's all in other spreadsheets, you're kind of limited. If it's in a proper database, you can create a view which is a customized table-like representation of related data from other tables. If you let the database engine do the heavy lifting, there's less to do once you get it into Excel.

MS Query I have a hate/hate relationship with. It never seems to work out quite right. I don't know if I'm missing something or it's just limited and lacking.

 

[SkipVought]

FYI, I use MS Query every day, to get data from Excel, Oracle, DB2, MS Access, Text Files.

Works GREAT!!!

Quick answers!!!

Gotta know a bit of SQL to make it really sing.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

As far as a formula goes, I'd use CountIf. It takes up less resources than VLookup. Anything that returns >0 is in the other list.



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

...and to get your list of customer ids, you can use the AdvancedFilter (Data>Filter>Advanced Filter)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[anthony777]

I have a spreadsheet book 1 with a list of customer id in field custid I have another spreadsheet called June 2009.xls with cust id I want to see if the custid in book 1 does exist in June 2009.xls cust id. I am trying to use this forumula but having trouble can anyone help with the syntax =NOT(ISNA(MATCH(cust_id,other_list_ref,0)))and where do I put the formula I know it goes in book 1 my custid is in cell a so does this go in cell b and having trouble with the other-list-ref would that be June 2009.xls

 

[GlennUK]

Yes, it goes in cell b ( B1 maybe?, I'm guessing here, as "b" would normally refer to an entire column ).

If you navigate to the area with the cust id ( in June 2009.xls ), while you are building the formula, you won't get any spelling mistakes.

An example would be to type:
=NOT(ISNA(MATCH(A1,
then navigate to the June 2009.xls book, and point at the cell range that contains the cust id. It will generate the reference in the formula, and be something similar to this:
=NOT(ISNA(MATCH(A1,'[June 2009.xls]Data Sheet'!$E$4:$E$17
after which you simply type the end of the formula ,0))) before pressing Enter.

Cheers, Glenn.

Beauty is in the eye of the beerholder.