VLOOKUP Help

[furstukin]

I am trying to create a customer order form that will allow you to choose the phone number of an existing customer and will populate the name and address for you.

I have a customer list that looks like this

<TABLE style="WIDTH: 653pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=869><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=103>John</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=154>Doe</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 100pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=133>123-456-7890</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 154pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=205>123 N 321 st</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=118>Quahog</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=75>RI</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=81 align=right>12345</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; FONT-FAMILY: Arial; BACKGROUND: #bfbfbf; HEIGHT: 12.75pt; COLOR: windowtext; FONT-SIZE: 10pt; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl65 height=17>Jane</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #bfbfbf; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64>Doe</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #bfbfbf; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64>123-567-8901</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #bfbfbf; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64>321 S 123 st</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #bfbfbf; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64>Quahog</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #bfbfbf; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl64>RI</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: #bfbfbf; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: black none" align=right>12345</TD></TR></TBODY></TABLE>
And I have a list created for the phone numbers and a validation check on a different sheet in cell B10 where I can choose the phone number from a drop down.

In the first name cell I have =VLOOKUP(B10,'Existing Customer'!A10:H700,1,0) but it always returns as #N/A

Am I missing something?

Also in my validation check in B10 if I start typing a phone number it does not try to guess at what I am typing. Is there a way to make it do that too?

Thanks for any help you can provide.

 

[VoG]

VLOOKUP cannot look left. Try like this

=INDEX(A:A,MATCH(C10,B:B,0))

 

[furstukin]

VLOOKUP cannot look left. Try like this

=INDEX(A:A,MATCH(C10,B:B,0))

Well it took a little figuring out how to make that work. But here is what i did and it seems to work. Thank you for solving that for me.

=INDEX('Customer List'!D:D,MATCH(B10,'Customer List'!C:C,0))

Now all I need to do is figure out how to get excel to guess at the phone numbers I type as I type them or at least jump to the spot in the list closest to what i have entered. Like if you have a huge drop down and you hit "B" it jumps to the first item in the list that starts with "B"