lookup function error

richfm1

New Member
Joined
Aug 23, 2010
Messages
28
I continue to get the wrong results when using vlookup, hlookup, and index/match. the table i am using is:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=18 width=64> A
1 Key
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=64>B
key2
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>2 cyw</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">CWI</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>3 nex</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">EXA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>4 flx</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">FLX</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>5 fpd</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">FPG</TD></TR></TBODY></TABLE>

I need the formula to return the value in c1 that corresponds to value in col B... ie if c1 is "fpd" then d1 (what contains the formula) returns "fpg". if the value in c1 changes to "nex" then d1 returns "exa" and so on...

your help is appreciated
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
I continue to get the wrong results when using vlookup, hlookup, and index/match. the table i am using is:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=18 width=64>A
1 Key

</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=64>B
key2

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>2 cyw</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">CWI</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>3 nex</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">EXA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>4 flx</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">FLX</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=17>5 fpd</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3">FPG</TD></TR></TBODY></TABLE>

I need the formula to return the value in c1 that corresponds to value in col B... ie if c1 is "fpd" then d1 (what contains the formula) returns "fpg". if the value in c1 changes to "nex" then d1 returns "exa" and so on...

your help is appreciated

Assuming that Key and key2 are just headers...

D1, just enter and copy down:

=INDEX($B$2:$B$5,MATCH(C1,$A$2:$A$5,0))

Alternatively...

=VLOOKUP(C1,$A$2:$B$5,2,0)

Both formulas would return #N/A if the value
we look up (values in column C) are not available in column A.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Like this?

Excel Workbook
ABCD
2cywCWIfpdFPG
3nexEXA
4flxFLX
5fpdFPG
Sheet5
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Check for spaces on the end of your column A data.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,521
Messages
5,529,322
Members
409,863
Latest member
stacy09
Top