vvlookup and cell format

**johnnyjomp** · 04-21-2020, 05:30 PM

Hello everyone
When I use vlookup from one spreadsheet to another, it works fine. Today I have a price list that was emailed in the body of an email. I can copy and paste it into an excel sheet, with columns, but I cannot get vlookup to work. After a TREMENDOUS amount of fiddling with cell formats and =text formula, I was able to get some of the part numbers to work with vlookup, but only the ones that are all numerals. None of the part numbers that contain letters and dashes work (I get #NA).

I am certain the issue is the number formatting on the spreadsheet I created by copy and pasting from the email body. How do I fix this???

Thanks in advance for any help you can offer because it is driving me crazy. or crazier.

John

EDIT:
I was able to get this to work, but would love to know what the issue was. I started with new spreadsheeets. the part numbers with dashes and letters worked fine off the bat. To make the cells that were all numerals work, I had to double click inside each cell. Why did double clicking each cell make a difference?

**BMV** · 04-22-2020, 01:31 AM

There are several ways
1. convert text value to number
check column format . It must be not text. in a cell enter 1 or 0 copy it and paste to you data area with Past special. Use Multyply or Add (for 1 or 0)
2. Use two level of Vlookup
=iferror(vlookup(value,,,),vllokup(value&"",,,))

**Pepe Le Mokko** · 04-22-2020, 02:32 AM

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

**johnnyjomp** · 04-22-2020, 10:43 AM

Originally Posted by BMV

There are several ways
1. convert text value to number
check column format . It must be not text. in a cell enter 1 or 0 copy it and paste to you data area with Past special. Use Multyply or Add (for 1 or 0)
2. Use two level of Vlookup
=iferror(vlookup(value,,,),vllokup(value&"",,,))

Hello BMV.
thank you for the reply.

I spent a lot of time trying to convert numbers to text because the only numbers that worked were marked as "number stored as text". I created new spreadsheets and I got it all to work by double clicking the cells where there were numbers (no text or dashes) and turning them into "number stored as text".

All so strange that I never had this problem before!

Thank you again for your reply
John

vvlookup and cell format

LinkBack

Thread Tools

Rate This Thread

Display

vvlookup and cell format

Re: vvlookup and cell format

Re: vvlookup and cell format

Re: vvlookup and cell format

Thread Information

Users Browsing this Thread

Similar Threads

Change cell format/value based on the value of another cell (time format transformation)

Format cell/text color via VBA and supercede manual cell formatting like Cond.Format does

Use format of one cell to change type of format used in conditional format

Conditionally format a cell based on multiple criteria including format cell

[SOLVED] take a cell value of nixed format and multiply another cell of mixed format

How do I copy data in single cell format to a merged cell format

[SOLVED] How do I throw in an ISERROR function on a complicated VVLOOKUP?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions