date error in VLookup

edward91

New Member
Joined
Jun 29, 2010
Messages
24
Hi just a short question.how come when i use vlookup using date as the lookup value it display the #N/A ? can help ?

69663847.jpg
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
When using VLOOKUP, you can only search one column and return a value from a column to the RIGHT of that column. Based on your formula, you're looking up the date in D10 in a table spanning A1:I6, so VLOOKUP is going to search column A for that date, and return the value from the second column.

Of course, this doesn't work because column A in your table doesn't contain dates. To lookup a value in one column and return a value from a column to the LEFT of it, use INDEX/MATCH, like so:

=INDEX($B$2:$B$6,MATCH(D10,$I$2:$I$6,0))

This searches I2:I6 for the value in D10, and if found will return the value from column B in the same row as the match. Change your ranges accordingly.
 

edward91

New Member
Joined
Jun 29, 2010
Messages
24
opps sry..forget to add in..i using excel version 2003. thank for your help i will try it when i in office tml..as my home 2007 cannot be used to open e file .thx
 

edward91

New Member
Joined
Jun 29, 2010
Messages
24
HI then, how i do i make a 2nd occurance,3rd occurance etc base on this one?
 

shemayisroel

Well-known Member
Joined
Sep 11, 2008
Messages
1,867

edward91

New Member
Joined
Jun 29, 2010
Messages
24
HI just wondering if there is a method that is related to
=INDEX(Data!$C$5:$C$200,MATCH(F9&M6&N6,Data!$G$5:$G$200&Data!$A$5:$A$200&Data!$O$5:$O$200,0))

cus i dunnoe understand e link u send me. Sry
 

edward91

New Member
Joined
Jun 29, 2010
Messages
24

ADVERTISEMENT

=INDEX(Data!$C$5:$C$200,MATCH(F9&M6&N6,Data!$G$5:$G$200&Data!$A$5:$A$200&Data!$O$5:$O$200,0))


hi this is my latest formula using multiple criteria, how can i modify it to display multiple result?

example

Raw data

a / b / c
Group/ Name / Date
grp1 Apple 1/1/2010
grp2 boy 1/2/2010
grp1 cat 1/1/2010


currently

1/1/2010 / grp1 /

Apple --(result) using =the formula stated above


wat i want

Apple
cat ---to be displayed out
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Hi edward,

Assume your raw data occupies A1:C5, Enter grp1 in D1, enter date in E1
Place formula in A7
=index($b$2:$b$4,small(if($a2:$a$4=$d$1,if($c$2:$c$4=$e$1,row($b$2:$b$4)-row($b$2)+1)),rows($a$7:a7))). Use CSE copy down.

This will list all items meeting criteria of grp1 and specific date

HTH
Mike Szczesny
 

Watch MrExcel Video

Forum statistics

Threads
1,123,480
Messages
5,601,911
Members
414,482
Latest member
morkar

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
Top