Vlookup Help

[josros60]

Hi

i have the table below and this vlookup formula but for the row 3 works fine but one and two doesn't pull the the data is blank.

=IF(ISERROR(VLOOKUP($D$6,Vendors!$A:$J,2,FALSE)),"",VLOOKUP($D$6,Vendors!$A:$J,2,FALSE))

[TABLE="width: 638"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Vendor[/TD]
[TD]Bill Cycle[/TD]
[TD]Notes[/TD]
[TD]Bank #[/TD]
[TD]Transit[/TD]
[TD]Acct #[/TD]
[TD][/TD]
[TD] Contact Name[/TD]
[TD]CC Email[/TD]
[/TR]
[TR]
[TD]100001[/TD]
[TD]Benton[/TD]
[TD][/TD]
[TD]auto pay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100002[/TD]
[TD]Helli[/TD]
[TD]n30[/TD]
[TD]auto pay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100003[/TD]
[TD]Canada Post[/TD]
[TD]varies[/TD]
[TD]auto pay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

can any tell me what am i doing wrong.

thanks

 

[jkpieterse]

First of all, if you are copying this formula down, adjust it to:

=IF(ISERROR(VLOOKUP(D6,Vendors!$A:$J,2,FALSE)),"",VLOOKUP(D6,Vendors!$A:$J,2,FALSE))

Secondly, the IFERROR function is more efficient as it avoids doing the VLOOKUP twice:

=IFERROR(VLOOKUP(D6,Vendors!$A:$J,2,FALSE),"")

I think your problem is that either the values in column D are numbers and the values in Vendors!A:A are text, or the other way around.

 

[josros60]

Hi,

i changed the formula to =IFERROR(VLOOKUP(D6,Vendors!$A:$J,2,FALSE),"") and then changed first two to D6 to text format and it worked the first two but not the third Canada post and checked the rest of numbers and all are formatted as text value, cannot figured out why doesn't work.

Thank you very much for your suggestions.

 

[jkpieterse]

They may be formatted as text, but changing the format does not change the content of the cells, after changing the format you have to re-enter the content (F2 followed by enter) to convince Excel the content of the cell must match the formatting.