Help with formula...

[veespike]

I am using a formula to lookup address information and paste it into a sheet, and need help fixing an error.

This is the formula:

=IF($H$20=0,"",VLOOKUP($H$20,'Nashville Lookup Tables.xls'!NashvilleCust,6,TRUE)&", "& VLOOKUP($H$20,'Nashville Lookup Tables.xls'!NashvilleCust,7,TRUE)&"  "& VLOOKUP($H$20,'Nashville Lookup Tables.xls'!NashvilleCust,8,FALSE))

This formula throws a #VALUE error on the last statement. The referenced column in the table has zip codes in it.

I tried converting it with a TEXT statement, but either I was entering it wrong, or that is the wrong method.

 

[Andrew Poulsom]

Try changing the FALSE to TRUE, like in the other VLOOKUPs. FALSE expects an exact match.

 

[davidhall80]

Andrew

I use a 0 instead of TRUE and FALSE. What's the difference between using a 0 or TRUE and FALSE for the lookups?

 

[Andrew Poulsom]

Andrew

I use a 0 instead of TRUE and FALSE. What's the difference between using a 0 or TRUE and FALSE for the lookups?

In Excel FALSE evaluates to 0, and TRUE to 1.

 

[veespike]

Try changing the FALSE to TRUE, like in the other VLOOKUPs. FALSE expects an exact match.

I tried this, and it did not work. It still reports a #VALUE error.

I actually got it to return a value and not an error with this:

=IF($H$20=0,"",VLOOKUP($H$20,'C:\Nashville Lookup Tables.xls'!NashvilleCust,6,true)&", "& VLOOKUP($H$20,'C:\Nashville Lookup Tables.xls'!NashvilleCust,7,true)&"  "& (TEXT(VLOOKUP($H$20,'C:\Nashville Lookup Tables.xls'!NashvilleCust,8,true),"Zip Code")))

But the formula returns something totally odd this way. The value in place s/b "Saint Charles, MO 63303" What I get back is "Saint Charles, MO Zip Co242073"

I also tried changing the data in the table from numerical to text format, and that did not work. Everything I have read tells me that the problem is trying to concatenate text and numerical data at the same time.

 

[Andrew Poulsom]

There is no problem concatenating text and numbers, eg:

="Saint Charles, MO "&63303

But the format for Zip Code isn't "Zip Code" but "00000". The formula:

=TEXT(63303,"Zip Code")

returns #VALUE!, but:

=TEXT(63303,"00000")

returns 63303.

 

[veespike]

There is no problem concatenating text and numbers, eg:

="Saint Charles, MO "&63303

But the format for Zip Code isn't "Zip Code" but "00000". The formula:

=TEXT(63303,"Zip Code")

returns #VALUE!, but:

=TEXT(63303,"00000")

returns 63303.

Ah! That works. Thank you.