VLookup Help

[staceysiepmann]

Hi,

I am having trouble with doing a VLOOKUP function between tw
workbooks. This is the nested function in whole:

=IF(ISNA(VLOOKUP(B2,'[Cross Reference through 10310
payroll2.xls]Cross-reference'!$A$2:$B$15201,1,FALSE)),"Tea
Member","Not Team Member")

Cell B2 is in the workbook that I want to have the VLOOKUP return th
value of Team Member or Not Team Member. Cross Reference through 10310
payroll2.xls is the other workbook and Cross-reference is the workshee
in the workbook that the VLOOKUP is checking. Currently the complet
formula is only returning a value of Team Member for all cells th
formula is pasted into in the master workbook.

I use my personal information as the control because I am not a tea
member and I should get a Not Team Member label in the VLOOKUP cell. I
returns with the label Team Member.

Can someone help me?

Thank

 

[Ron Rosenfeld]

Hi,

I am having trouble with doing a VLOOKUP function between two
workbooks. This is the nested function in whole:

=IF(ISNA(VLOOKUP(B2,'[Cross Reference through 103103
payroll2.xls]Cross-reference'!$A$2:$B$15201,1,FALSE)),"Team
Member","Not Team Member")

Cell B2 is in the workbook that I want to have the VLOOKUP return the
value of Team Member or Not Team Member. Cross Reference through 103103
payroll2.xls is the other workbook and Cross-reference is the worksheet
in the workbook that the VLOOKUP is checking. Currently the complete
formula is only returning a value of Team Member for all cells the
formula is pasted into in the master workbook.

I use my personal information as the control because I am not a team
member and I should get a Not Team Member label in the VLOOKUP cell. It
returns with the label Team Member.

Can someone help me?

Thanks

First of all, you are testing for an error return of VLOOKUP. If VLOOKUP
returns #NA!, then "Team Member" is the result. Perhaps you want the opposite?

Second -- what is in column A and column B in [Cross Reference ... ]??

Your formula has you looking for the matching name in column A.


--ron

 

[Jon Barchenger[MS]]

Good morning Stacy -

We could spend a lot of time with me trying to figure out exactly what you are doing in your sheets, before
I could start to address you question.
Would it be possible for you to either ZIP or STUFF a copy of the file and send it to me to work on?

I think the resolution is going to be rather quick - but if I don't have your workbook - I need to create somrthing similar
in order to verify that the solution works.

Thanks,
Jon Barchenger
--------------------
| **From: staceysiepmann <[email protected]>
| **Subject: VLookup Help
| **Date: Thu, 13 Nov 2003 08:56:01 -0600
| **Message-ID: <[email protected]>
| **Organization: ExcelTip
| **User-Agent: ExcelTipForum
| **X-Newsreader: ExcelTipForum
| **X-Originating-IP: 198.74.20.77
| **Newsgroups: microsoft.public.excel.worksheet.functions
| **NNTP-Posting-Host: 69-56-172-122.theplanet.com 69.56.172.122
| **Lines: 1
| **Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| **Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.worksheet.functions:171044
| **X-Tomcat-NG: microsoft.public.excel.worksheet.functions
| **
| *

Hi,
| **
| **I am having trouble with doing a VLOOKUP function between tw

workbooks. This is the nested function in whole:
| **
| **=IF(ISNA(VLOOKUP(B2,'[Cross Reference through 10310

payroll2.xls]Cross-reference'!$A$2:$B$15201,1,FALSE)),"Tea

Member","Not Team Member")
| **
| **Cell B2 is in the workbook that I want to have the VLOOKUP return th

value of Team Member or Not Team Member. Cross Reference through 10310

payroll2.xls is the other workbook and Cross-reference is the workshee

in the workbook that the VLOOKUP is checking. Currently the complet

formula is only returning a value of Team Member for all cells th

formula is pasted into in the master workbook.
| **
| **I use my personal information as the control because I am not a tea

member and I should get a Not Team Member label in the VLOOKUP cell. I

returns with the label Team Member.
| **
| **Can someone help me?
| **
| **Thank





-----------------------------------------------






| **

 

[Robert]

The vlookup function is to return a result from a table by
finding a match in column 1 and returning the contents of
a cell from column 2,3 or 4 (which ever column you tell it)
which is on the same row as the match. The first column in
the table is the column you are matching to B2 so the
return result you are looking for is from column 2,3 or 4
on that row where the match was found. In your formula you
are asking it to return the value from column 1 which is
the value of B2.
Why you are doing an ISNA I am not sure I would think the
result from the vlookup would give you "Team Member"
or "Non Team Member". If you are trying to have a positive
result tell you "Team Memeber" and a N/A result tell
you "Non Team Member" then changing the 1 to a 2 or 3
should get you the result desired.

 

[staceysiepmann]

Ron, Jon and Robert,

Thanks for all your help.

Before I received your replies, I did reverse "not team member" an
"team member" and had the VLOOKUP look in only column "B" rather tha
both columns "A" and "B." (The example I was using had the VLOOKU
searching two columns.) These actions resolved the issues I wa
having.

Pretty minor stuff, all in all, but since it was the first time I ha
written a nested function and a VLOOKUP, I think I did okay.

I would not have been able to send the files because they ar
proprietary information. Besides, one is 5 mgs and the other is 1 mg
They would have most likely crashed the system.

Stace

 

[Ron Rosenfeld]

Ron, Jon and Robert,

Thanks for all your help.

Before I received your replies, I did reverse "not team member" and
"team member" and had the VLOOKUP look in only column "B" rather than
both columns "A" and "B." (The example I was using had the VLOOKUP
searching two columns.) These actions resolved the issues I was
having.

Pretty minor stuff, all in all, but since it was the first time I had
written a nested function and a VLOOKUP, I think I did okay.

I would not have been able to send the files because they are
proprietary information. Besides, one is 5 mgs and the other is 1 mg.
They would have most likely crashed the system.

Stacey

Thank you for the followup. It's nice to know what works and what doesn't.


--ron