VLOOKUP Problem

L

Leslie

I'm not familar enough with the functions in Excel and need some help. I
have two worksheets and I'm trying to use the 'vlookup' on my 1st worksheet
to look up data on a imported table located on sheet two.

First worksheet has a table that looks like this:
CLAIM NO MEMBER NO
104068 =VLOOKUP([Claim No],Table_S1019203_CTCNK02[NKCLMN],3,FALSE)

Second worksheet has a talbe that look like this:
NKCLMN NKICNO NKCUST
104053 90789 547811
4439 87689 208176
104068 40129 630085

I am trying to pull over the field [NKCUST] to [Member No] and I apparently
have my formula wrong because all I get is '#Ref'.
 
J

JE McGimpsey

Is Table_S1019203_CTCNK02 a defined name for the table? The worksheet
name? Something else?

If a defined name (assuming it's defined correctly):

=VLOOKUP(A2,Table_S1019203_CTCNK02, 3, FALSE)


If the worksheet name:

=VLOOKUP(A2,Table_S1019203_CTCNK02!A:C, 3, FALSE)

If something else, post back with more info.
 
N

NPell

I'm not familar enough with the functions in Excel and need some help.  I
have two worksheets and I'm trying to use the 'vlookup' on my 1st worksheet
to look up data on a imported table located on sheet two.

First worksheet has a table that looks like this:
CLAIM NO   MEMBER NO
  104068   =VLOOKUP([Claim No],Table_S1019203_CTCNK02[NKCLMN],3,FALSE)

Second worksheet has a talbe that look like this:
NKCLMN   NKICNO   NKCUST
104053    90789   547811
  4439    87689   208176
104068    40129   630085

I am trying to pull over the field [NKCUST] to [Member No] and I apparently
have my formula wrong because all I get is '#Ref'.
Click to expand...

Can you supply Cell References, like Rows and Columns. I can make an
exact formula for you then.
 
N

NPell

I'm not familar enough with the functions in Excel and need some help.  I
have two worksheets and I'm trying to use the 'vlookup' on my 1st worksheet
to look up data on a imported table located on sheet two.

First worksheet has a table that looks like this:
CLAIM NO   MEMBER NO
  104068   =VLOOKUP([Claim No],Table_S1019203_CTCNK02[NKCLMN],3,FALSE)

Second worksheet has a talbe that look like this:
NKCLMN   NKICNO   NKCUST
104053    90789   547811
  4439    87689   208176
104068    40129   630085

I am trying to pull over the field [NKCUST] to [Member No] and I apparently
have my formula wrong because all I get is '#Ref'.
Click to expand...

Im going home soon, so i used it as standard.. Assuming its this..

A B C
1 NKCLMN NKICNO NKCUST
2 104053 90789 547811
3 4439 87689 208176
4 104068 40129 630085

A B
1 CLAIM NO MEMBER NO
2 104053 *****


To stop the formula splitting lines, its where stars are and its;

=VLOOKUP(A2,Sheet1!A:C,3,FALSE)
 
L

Leslie

I have used the 'Define Name' to name tables and also named the worksheets as
such:
worksheet1 name = SubroLog
worksheet 1 has a table with defined table named 'subrolog'

A B
Headers ClaimNo MemberNo
104068 =VLOOKUP(SubroLog![ClaimNo],CTCNK02![NKCLMN],3,FALSE)

worksheet2 name = CTCNK02
worksheet 2 has a table with defined table named 'CTCNK02'

A B C
Headers NKCLMN NKICNO NKCUST
469 3490 102899
23409 860 32340
104068 4821 603781

I want the function to use the claim number I type into the first
worksheet(SubroLog) to lookup the matching claim number on the second
worksheet2(CTCNK02) and populate the customer number from worksheet2(CTCNK02)
column 3

end result for my 'SubroLog' would like like the following:
A B
Headers ClaimNo MemberNo
104068 603781

Am I even using the correct lookup? (vlookup or hlookup)



NPell said:
I'm not familar enough with the functions in Excel and need some help. I
have two worksheets and I'm trying to use the 'vlookup' on my 1st worksheet
to look up data on a imported table located on sheet two.

First worksheet has a table that looks like this:
CLAIM NO MEMBER NO
104068 =VLOOKUP([Claim No],Table_S1019203_CTCNK02[NKCLMN],3,FALSE)

Second worksheet has a talbe that look like this:
NKCLMN NKICNO NKCUST
104053 90789 547811
4439 87689 208176
104068 40129 630085

I am trying to pull over the field [NKCUST] to [Member No] and I apparently
have my formula wrong because all I get is '#Ref'.
Click to expand...

Im going home soon, so i used it as standard.. Assuming its this..

A B C
1 NKCLMN NKICNO NKCUST
2 104053 90789 547811
3 4439 87689 208176
4 104068 40129 630085

A B
1 CLAIM NO MEMBER NO
2 104053 *****


To stop the formula splitting lines, its where stars are and its;

=VLOOKUP(A2,Sheet1!A:C,3,FALSE)
Click to expand...
 
L

Leslie

I did figure the formula using 'defined names' in 2007
I had to first define my names as such:
myclaimno=Table2[[#This Row],[Claim No]]
systemclaimno=Sheet2!$1:$1048576
mymembername=3
Then for my cell I used the following formula:
=VLOOKUP(myclaimno,systemclaimno,mymembername,FALSE)

Leslie said:
I have used the 'Define Name' to name tables and also named the worksheets as
such:
worksheet1 name = SubroLog
worksheet 1 has a table with defined table named 'subrolog'

A B
Headers ClaimNo MemberNo
104068 =VLOOKUP(SubroLog![ClaimNo],CTCNK02![NKCLMN],3,FALSE)

worksheet2 name = CTCNK02
worksheet 2 has a table with defined table named 'CTCNK02'

A B C
Headers NKCLMN NKICNO NKCUST
469 3490 102899
23409 860 32340
104068 4821 603781

I want the function to use the claim number I type into the first
worksheet(SubroLog) to lookup the matching claim number on the second
worksheet2(CTCNK02) and populate the customer number from worksheet2(CTCNK02)
column 3

end result for my 'SubroLog' would like like the following:
A B
Headers ClaimNo MemberNo
104068 603781

Am I even using the correct lookup? (vlookup or hlookup)



NPell said:
I'm not familar enough with the functions in Excel and need some help. I
have two worksheets and I'm trying to use the 'vlookup' on my 1st worksheet
to look up data on a imported table located on sheet two.

First worksheet has a table that looks like this:
CLAIM NO MEMBER NO
104068 =VLOOKUP([Claim No],Table_S1019203_CTCNK02[NKCLMN],3,FALSE)

Second worksheet has a talbe that look like this:
NKCLMN NKICNO NKCUST
104053 90789 547811
4439 87689 208176
104068 40129 630085

I am trying to pull over the field [NKCUST] to [Member No] and I apparently
have my formula wrong because all I get is '#Ref'.
Click to expand...

Im going home soon, so i used it as standard.. Assuming its this..

A B C
1 NKCLMN NKICNO NKCUST
2 104053 90789 547811
3 4439 87689 208176
4 104068 40129 630085

A B
1 CLAIM NO MEMBER NO
2 104053 *****


To stop the formula splitting lines, its where stars are and its;

=VLOOKUP(A2,Sheet1!A:C,3,FALSE)
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is there any way of combining vlookup with getpivotdata? 1
VLOOKUP & INDEX/MATCH trouble 3
Vlookup Problem 2
not following your answer 3

Top