VLOOKUP only getting first line of data

E

electronictokwa

Board Regular
Joined
Oct 25, 2011
Messages
122
This is just a portion of where I am trying to do vlookup from another sheet. The lookup value is on the first column, and returned value is on the second column. Problem is, I am only getting the result for 1/1/2012, 1/2/2012 down are returning errors.

The VLOOKUP formula from the search tab:

=IFERROR(VLOOKUP(G4,DBList!B6:C370,2,0),"na")

I also used =WEEKNUM(B370,1) & " " & 2012 for the second column below to automatically count the workweek. Any ideas?


<table class="tableizer-table"> <tbody><tr class="tableizer-firstrow"><th>1/1/2012</th><th>1 2012</th><th>January 2012</th></tr> <tr><td>1/2/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/3/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/4/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/5/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/6/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/7/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/8/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/9/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/10/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/11/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/12/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/13/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/14/2012</td><td>2 2012</td><td>January 2012</td></tr></tbody></table>
 
Sort by date Sort by votes
GK039 said:
Could you post a sample of your workbook?

VLOOKUP returns only the first item out of dups values if that's what your problem is.

George
Click to expand...

Hi George, yes I think this is the problem since there are dups as you can see on the table. Is there a workaround? I'm sorry but I cant post a copy of the workbook :(
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
electronictokwa said:
All cells except for the one with 1/1/2012 (cell B370). The formula to count the workweek =WEEKNUM(B370,1) & " " & 2012 is in cell C370. VLOOKUP is returning the value in this cell but not for the other cells; C371 down. Again, here are the tables:

This is the vlookup sheet:

<STYLE type=text/css>
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</STYLE><STYLE type=text/css>
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</STYLE>

<TABLE class=tableizer-table>
<TBODY><TR class=tableizer-firstrow><TH>Date Monitored</TH><TH>1/2/2012</TH></TR><TR><TD>Work week</TD><TD>NA</TD></TR><TR><TD>Evaluation Type</TD><TD></TD></TR>
</TABLE>



<TABLE class=tableizer-table><TBODY><TR class=tableizer-firstrow><TH>1/1/2012</TH><TH>1 2012</TH><TH>January 2012</TH></TR><TR><TD>1/2/2012</TD><TD>1 2012</TD><TD>January 2012</TD></TR><TR><TD>1/3/2012</TD><TD>1 2012</TD><TD>January 2012</TD></TR><TR><TD>1/4/2012</TD><TD>1 2012</TD><TD>January 2012</TD></TR><TR><TD>1/5/2012</TD><TD>1 2012</TD><TD>January 2012</TD></TR><TR><TD>1/6/2012</TD><TD>1 2012</TD><TD>January 2012</TD></TR><TR><TD>1/7/2012</TD><TD>1 2012</TD><TD>January 2012</TD></TR><TR><TD>1/8/2012</TD><TD>2 2012</TD><TD>January 2012</TD></TR><TR><TD>1/9/2012</TD><TD>2 2012</TD><TD>January 2012</TD></TR><TR><TD>1/10/2012</TD><TD>2 2012</TD><TD>January 2012</TD></TR><TR><TD>1/11/2012</TD><TD>2 2012</TD><TD>January 2012</TD></TR><TR><TD>1/12/2012</TD><TD>2 2012</TD><TD>January 2012</TD></TR><TR><TD>1/13/2012</TD><TD>2 2012</TD><TD>January 2012</TD></TR><TR><TD>1/14/2012</TD><TD>2 2012</TD><TD>January 2012

</TD></TR></TBODY></TABLE>
Click to expand...


So your lookup values is the date in bold?? Date Monitored?
 
Upvote 0
electronictokwa said:
All cells except for the one with 1/1/2012 (cell B370). The formula to count the workweek =WEEKNUM(B370,1) & " " & 2012 is in cell C370. VLOOKUP is returning the value in this cell but not for the other cells; C371 down. Again, here are the tables:

This is the vlookup sheet:

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style><style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>


<table class="tableizer-table"> <tbody><tr class="tableizer-firstrow"><th>Date Monitored</th><th>1/2/2012</th></tr> <tr><td>Work week</td><td>NA</td></tr> <tr><td>Evaluation Type</td><td>
</td></tr></tbody></table>



<table class="tableizer-table"> <tbody><tr class="tableizer-firstrow"><th>1/1/2012</th><th>1 2012</th><th>January 2012</th></tr> <tr><td>1/2/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/3/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/4/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/5/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/6/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/7/2012</td><td>1 2012</td><td>January 2012</td></tr> <tr><td>1/8/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/9/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/10/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/11/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/12/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/13/2012</td><td>2 2012</td><td>January 2012</td></tr> <tr><td>1/14/2012</td><td>2 2012</td><td>January 2012
</td></tr></tbody></table>
Click to expand...


what is your desired result? date? Work week? and what result you want in work week?
 
Upvote 0
kamran02 said:
what is your desired result? date? Work week? and what result you want in work week?
Click to expand...

Here is the working part:

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Date Monitored</th><th>1/1/2012</th></tr> <tr><td>Work week</td><td>1 2012</td></tr> <tr><td>Evaluation Type</td><td></td></tr></table>

And when 1/2/2012, or any other date is entered, here is the result which contains "NA":

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Date Monitored</th><th>1/2/2012</th></tr> <tr><td>Work week</td><td>NA</td></tr> <tr><td>Evaluation Type</td><td></td></tr></table>
 
Upvote 0
electronictokwa said:
Here is the working part:

<STYLE type=text/css>
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</STYLE>

<TABLE class=tableizer-table>
<TBODY><TR class=tableizer-firstrow><TH>Date Monitored</TH><TH>1/1/2012</TH></TR><TR><TD>Work week</TD><TD>1 2012</TD></TR><TR><TD>Evaluation Type</TD><TD></TD></TR>
</TABLE>

And when 1/2/2012, or any other date is entered, here is the result which contains "NA":

<STYLE type=text/css>
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</STYLE>

<TABLE class=tableizer-table>
<TBODY><TR class=tableizer-firstrow><TH>Date Monitored</TH><TH>1/2/2012</TH></TR><TR><TD>Work week</TD><TD>NA</TD></TR><TR><TD>Evaluation Type</TD><TD></TD></TR>
</TABLE>
Click to expand...


I made a copy of what you posted and test it. It works fine..!!! Vlookup fetches the result you want
 
Upvote 0
electronictokwa said:
Here is the working part:

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>


<table class="tableizer-table"> <tr class="tableizer-firstrow"><th>Date Monitored</th><th>1/1/2012</th></tr> <tr><td>Work week</td><td>1 2012</td></tr> <tr><td>Evaluation Type</td><td>
</td></tr></table>

And when 1/2/2012, or any other date is entered, here is the result which contains "NA":

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>


<table class="tableizer-table"> <tr class="tableizer-firstrow"><th>Date Monitored</th><th>1/2/2012</th></tr> <tr><td>Work week</td><td>NA</td></tr> <tr><td>Evaluation Type</td><td>
</td></tr></table>
Click to expand...


Excel Workbook
ABC
1Date Monitored1/2/2012
2Work Week1 2012
3Work Week1 2012
4Evaluation
5
61/1/20121 2012January 2012
71/2/20121 2012January 2012
81/3/20121 2012January 2012
91/4/20121 2012January 2012
101/5/20121 2012January 2012
111/6/20121 2012January 2012
121/7/20121 2012January 2012
131/8/20122 2012January 2012
141/9/20122 2012January 2012
151/10/20122 2012January 2012
Sheet3


Try Both...
 
Upvote 0
kamran02 said:
Excel Workbook
ABC
1Date Monitored1/2/2012
2Work Week1 2012
3Work Week1 2012
4Evaluation
5
61/1/20121 2012January 2012
71/2/20121 2012January 2012
81/3/20121 2012January 2012
91/4/20121 2012January 2012
101/5/20121 2012January 2012
111/6/20121 2012January 2012
121/7/20121 2012January 2012
131/8/20122 2012January 2012
141/9/20122 2012January 2012
151/10/20122 2012January 2012
Sheet3


Try Both...
Click to expand...

Ok I will. Just give me a few minutes. By the way, thanks for the patience guys!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

staticfluids
  • Solved
Dynamic Array Formula (FILTER) But Show the Other Criteria with Blank
Replies
4
Views
129
staticfluids
staticfluids
N
  • Question
Find Latest comment for ID using vlookup/MAX?
Replies
1
Views
110
KOKOSEK
KOKOSEK
S
  • Solved
XLOOKUP with multiple criteria across multiple columns and rows
Replies
6
Views
109
soriaul
S
K
  • Solved
Reorder the 3 column data into 2 columns of 24 rows
Replies
6
Views
97
Fluff
Fluff
S
  • Solved
How to combine IF and FILTER formulas to copy text from one column to another
Replies
4
Views
261
Fluff
Fluff

Forum statistics

Threads
1,215,474
Messages
6,125,024
Members
449,204
Latest member
LKN2GO

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top