why Vlookup and match dont work on my excel 2010

[rapid2010]

i have been using excel 2010 for a few days now, but i have got a few problems about vlookup and match. they just keep displaying #NA

does it matter all cells are in gerenal?

=VLOOKUP(M2,A2:K7,2,FALSE)

M2= 55
A2:K7- A is reference number
B is name
D7 is same as M2 (55)

the formula above just wont work, it keep display #NA

 

[scottylad2]

Vlookup will only work if the value that its looking up is in a left most position in a table array, if you need a lookup to return from the columns other than the left most then Index and maych should do it. Can you provide more details of what your looking to return as an answer? A sample on the board will help

 

[rapid2010]

I cant upload a photo!

my data in the excel contains customer refer,name, address, payment methods, and amount.

55 is the house number
i want to find the name of the person lives in this house

 

[scottylad2]

HTML Jeannie will allow you to upload a sample

Excel Workbook
	E	F	G	H	I
21	name	House Number	address	payment methods	amount.
22	Mrs Smith	1	anytown, any city	Cash	101.00
23	Mr Jones	2	anytown, any city	Visa	85.00
24	Mrs Mack	3	anytown, any city	Cash	94.00
25	Mr Minto	4	anytown, any city	Visa	92.00
26	Mrs Maloney	5	anytown, any city	Visa	124.00
27
28		Lookup House number
29		3
30		Mrs Mack
Sheet2

I'm assuming your house numbers are in a different column, if not then another formula will be needed to extract the house number from the body of the address so it can be used as a lookup reference

 

[rapid2010]

<table width="1331" border="0" cellpadding="0" cellspacing="0"><col style="width: 91pt;" width="121" span="11"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl65" style="height: 30pt; width: 91pt;" width="121" height="40">Working Date</td> <td class="xl65" style="width: 91pt;" width="121">Name</td> <td class="xl65" style="width: 91pt;" width="121">Surename</td> <td class="xl65" style="width: 91pt;" width="121">House number</td> <td class="xl65" style="width: 91pt;" width="121">Street</td> <td class="xl65" style="width: 91pt;" width="121">Street 1</td> <td class="xl65" style="width: 91pt;" width="121">Town</td> <td class="xl65" style="width: 91pt;" width="121">City</td> <td class="xl65" style="width: 91pt;" width="121">Post Code</td> <td class="xl65" style="width: 91pt;" width="121">Payment Methods</td> <td class="xl65" style="width: 91pt;" width="121">Amounts</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 91pt;" width="121" height="20">05/06/2010</td> <td class="xl65" style="width: 91pt;" width="121">jay</td> <td class="xl65" style="width: 91pt;" width="121">kane</td> <td class="xl65" style="width: 91pt;" width="121">11</td> <td class="xl65" style="width: 91pt;" width="121">market street</td> <td class="xl65" style="width: 91pt;" width="121">hoylake</td> <td class="xl65" style="width: 91pt;" width="121">wirral</td> <td class="xl65" style="width: 91pt;" width="121">liverpool</td> <td class="xl65" style="width: 91pt;" width="121">ch47 3bb</td> <td class="xl65" style="width: 91pt;" width="121">cash</td> <td class="xl67" style="width: 91pt;" width="121">£500.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 91pt;" width="121" height="20">15/02/2011</td> <td class="xl65" style="width: 91pt;" width="121">jame</td> <td class="xl65" style="width: 91pt;" width="121">jones</td> <td class="xl65" style="width: 91pt;" width="121">1</td> <td class="xl65" style="width: 91pt;" width="121">school lane</td> <td class="xl65" style="width: 91pt;" width="121">morton</td> <td class="xl65" style="width: 91pt;" width="121">blackburn</td> <td class="xl65" style="width: 91pt;" width="121">merseyside</td> <td class="xl65" style="width: 91pt;" width="121">ch57 3ii</td> <td class="xl65" style="width: 91pt;" width="121">cheque</td> <td class="xl67" style="width: 91pt;" width="121">£100.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 91pt;" width="121" height="20">15/03/2011</td> <td class="xl65" style="width: 91pt;" width="121">kay</td> <td class="xl65" style="width: 91pt;" width="121">james</td> <td class="xl65" style="width: 91pt;" width="121">5</td> <td class="xl65" style="width: 91pt;" width="121">kane land</td> <td class="xl65" style="width: 91pt;" width="121">hoylake</td> <td class="xl65" style="width: 91pt;" width="121">earth</td> <td class="xl65" style="width: 91pt;" width="121">manchester</td> <td class="xl65" style="width: 91pt;" width="121">ch12 1ct</td> <td class="xl65" style="width: 91pt;" width="121">direct debt</td> <td class="xl67" style="width: 91pt;" width="121">£156.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 91pt;" width="121" height="20">16/03/2011</td> <td class="xl65" style="width: 91pt;" width="121">ryan</td> <td class="xl65" style="width: 91pt;" width="121">king</td> <td class="xl65" style="width: 91pt;" width="121">46</td> <td class="xl65" style="width: 91pt;" width="121">walker street</td> <td class="xl65" style="width: 91pt;" width="121">meols</td> <td class="xl65" style="width: 91pt;" width="121">hell</td> <td class="xl65" style="width: 91pt;" width="121">leeds</td> <td class="xl65" style="width: 91pt;" width="121">l4 s2i</td> <td class="xl65" style="width: 91pt;" width="121">cash</td> <td class="xl67" style="width: 91pt;" width="121">£475.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 91pt;" width="121" height="20">01/05/2011</td> <td class="xl65" style="width: 91pt;" width="121">ling</td> <td class="xl65" style="width: 91pt;" width="121">zoe</td> <td class="xl65" style="width: 91pt;" width="121">77</td> <td class="xl65" style="width: 91pt;" width="121">trinity road</td> <td class="xl65" style="width: 91pt;" width="121">biston</td> <td class="xl65" style="width: 91pt;" width="121">****</td> <td class="xl65" style="width: 91pt;" width="121">london</td> <td class="xl65" style="width: 91pt;" width="121">w1n</td> <td class="xl65" style="width: 91pt;" width="121">cheque</td> <td class="xl67" style="width: 91pt;" width="121">£45.00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 91pt;" width="121" height="20">03/06/2011</td> <td class="xl65" style="width: 91pt;" width="121">sue</td> <td class="xl65" style="width: 91pt;" width="121">wyn</td> <td class="xl65" style="width: 91pt;" width="121">55</td> <td class="xl65" style="width: 91pt;" width="121">newton road</td> <td class="xl65" style="width: 91pt;" width="121">birkenhead</td> <td class="xl65" style="width: 91pt;" width="121">wirral</td> <td class="xl65" style="width: 91pt;" width="121">darwent</td> <td class="xl65" style="width: 91pt;" width="121">ciy</td> <td class="xl65" style="width: 91pt;" width="121">cash</td> <td class="xl67" style="width: 91pt;" width="121">£22.00</td> </tr> </tbody></table>

<table width="242" border="0" cellpadding="0" cellspacing="0"><col style="width: 91pt;" width="121" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 91pt;" width="121" height="20">55</td> <td class="xl65" style="width: 91pt;" width="121">#REF!</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 91pt;" width="121" height="20">1</td> <td class="xl65" style="width: 91pt;" width="121">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 91pt;" width="121" height="20">11</td> <td class="xl65" style="width: 91pt;" width="121">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 91pt;" width="121" height="20">46</td> <td class="xl65" style="width: 91pt;" width="121">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 91pt;" width="121" height="20">5</td> <td class="xl65" style="width: 91pt;" width="121">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 91pt;" width="121" height="20">77</td> <td class="xl65" style="width: 91pt;" width="121">
</td> </tr> </tbody></table>

 

[Haseeb Avarakkan]

Hello, Welcome to the board. Try

=INDEX(Name_Col,MATCH(LookupHouse#,House_Number_Col,0))

Use the appropriate columns & cell.

 

[scottylad2]

Are you wanting both first and last name returned? this will get you the surname only, but can be joined with a similar formula to get the 1st and last name based on the house number

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
5	Working Date	Name	Surename	House number	Street	Street 1	Town	City	Post Code	Payment Methods	Amounts
6	05/06/2010	jay	kane	11	market street	hoylake	wirral	liverpool	ch47 3bb	cash	500.00
7	15/02/2011	jame	jones	1	school lane	morton	blackburn	merseyside	ch57 3ii	cheque	100.00
8	15/03/2011	kay	james	5	kane land	hoylake	earth	manchester	ch12 1ct	direct debt	156.00
9	16/03/2011	ryan	king	46	walker street	meols	hell	leeds	l4 s2i	cash	475.00
10	01/05/2011	ling	zoe	77	trinity road	biston	****	london	w1n	cheque	45.00
11	03/06/2011	sue	wyn	55	newton road	birkenhead	wirral	darwent	ciy	cash	22.00
12
13	55	wyn
14	1	jones
15	11	kane
16	46	king
17	5	james
18	77	zoe
Sheet1

The above is surname only

if you use the below formula with the same table you could extract 1st and last name

Sheet1

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 89.6px;"><col style="width: 81.6px;"></colgroup><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td>B</td><td>C</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td><td style="text-align: right; border: 1px solid rgb(0, 0, 0);">55</td><td style="border-top: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);">sue wyn</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">14</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0);">1</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);">jame jones</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">15</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0);">11</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);">jay kane</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">16</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0);">46</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);">ryan king</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">17</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0);">5</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);">kay james</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">18</td><td style="text-align: right; border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0);">77</td><td style="border-right: 1px solid rgb(0, 0, 0); border-bottom: 1px solid rgb(0, 0, 0);">ling zoe</td></tr></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C13</td><td>=INDEX($C$6:$C$11,MATCH(B13,$E$6:$E$11,0))&" "&INDEX($D$6:$D$11,MATCH(B13,$E$6:$E$11,0))</td></tr></table></td></tr></table>
The second formulas not displaying it properley but there is a space between the &" "& at the point the 2 formulas are being joined

 

[rapid2010]

why doesnt it work on mine?

mine is home and student 2010 non-commercial use!!!!

 

[scottylad2]

Theres no reason that formula should fail apart from if your numbers are stored as text. What answer are you getting?