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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,515
Messages
6,131,111
Members
449,621
Latest member
feaugcruz

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