Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Vlookup Format

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi All

Im having trouble with formatting when using vlookup.

If i add a listing from another spreadsheet (copy & paste) to the lookups array it doesnt register the new listings because it changes the format of the arrays cells. Is there any way around this. Ive tried various paste options and I tried using the index-match combo but this still didnt work.

Any ideas

Thanks in advance


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Format Cells as a Fraction in Excel Number Formatting
- This free Excel macro will automatically format a selected cell or many selected cells in the Fraction number format in
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Format Cells in The General (default) Format in Excel Number Formatting
- This free Excel macro formats a selected cell or range of cells to the General number format in Excel. This is the defa
Format Cells as a Percentage in Excel Number Formatting
- This free Excel macro formats a selection of cells as a Percentage in Excel. This simply changes a cell's number format

Similar Topics

I'm using a simple vlookup. The values I want results for (on the very left of my array) apparently do not have the same format as the ones I am looking up.

If I want them to produce a result, I have to copy and paste as a value in the array and only then will my vlookup will work.

It's not a formatting issue - because I've checked formats and they're both General.

Is there a way to make them comparable? Any help would be greatly appreciated.

I have a nested series of about 7 different lookup tables that work like this:


I changed the actual lookups to "# ARRAY" for simplicity's sake. The formula itself does what I need it to do, but I am wondering if there is a way for conditional formatting to indicate which array the result is being taken from. For example, if the result is being taken from the 1st array, color it blue. If it's being taken from the 2nd array, color it red, 3rd array, green, and so on and so forth. Can this be done?

how to format numbers stored as text or vice versa to use vlookup, match,

I have a spreadsheet that I am having supplied by other people.

It has part numbers, that are entered in the following way: 7-1405-01

I have the same part #'s on another spreadsheet that I maintain, that has
the same number as that.

The problem comes when I try to use a vlookup, match, or similiar
function....the format for some reason does not recognize each other from
spreadsheet to spreadsheet and I don't know how to get around this...

I have tried selecting all the part #'s and going to format cells and tried
to make sure that all the formatting of these part #'s match on each sheet,
be it, general, number, or text..but I still can't get it to work...

the only way I can get them to work is to retype the number, exactly as it
is again on the spreadsheet that the other people are supplying to me....when
I do this, even though the formatting is the same, then my function lookups

any idea how to get around this problem without having to retype all the
numbers on one of the sheets..

by the way, I'm using Excel 2003..


Ok, yet more fun! With the same prohject as before, I have realised that there are some issues...

Are there anyway of making a VLOOKUP conditional on whether or not there is actually an item where it is looking? Heres the coding and a better explanatoin:



Long isn't it....

So heres my problem, I want the last 5 VLOOKUPS only to happen if there is actually data for them to source from (Q3:U3) So if i put one of the tmers in Q3 and nothing in the others, it will still give me a result as opposed to '#N/A'

Any ideas guys??

I've seen this happen once before (on a shortest formula challenge)

I am working on a solution for

Every solution I have come up with does the same thing, it will work only if array-entered into multiple cells at the same time.

Formulas this holds true with a

Why is this? The formulas work perfectly, if entered into more than one cell (ANY number of cells). But do not return the right result when only entered into one.

I think VLOOKUP's don't work well in array format but INDEX/MATCH should as far as I know?


My question is: what differentiates 1 cell from another for vlookup.

I have a column of cells, of which there are 2 "PC"s. Next to it are values in column B. As far as I know, vlookup always finds the first PC, and picks up the value associated with it.

But somehow in the formatting, although they LOOK identical, vlookup differentiates the two "PC"s.

If I copy/paste the first PC, it returns the first value associated with it. If i copy/paste the 2nd PC, it returns the value associated. I looked at the format cells, and nothing in it that would differntiate the two.

Could someone help me figure this out? THey look identical, but yet, Vlookup treats them differently. What could do this to Vlookup?

Thank you.

Hi All,

I'm using the index/match/array combo to successfully pull data from a spreadsheet but need help understanding the process. The formula works but I'm lost.

Using this code I was able to get the array to work:



But why the "1" in the lookup_value for match? Does multipling the two arrays in the array lookup mean &?

Full example here (example 4 near the bottom):

Thanks a ton...


I'm having a problem using Index/Match or Vlookup with a dataset and would appreciate some help. A shortened version of the dataset is below:

A B C 1 Light/Rich Delicate/Smokey Type 2 1 1 Arbdeg 10 year 3 2 1 Laphroig 10 year 4 3 1 Laphroig Quarter Cask 5 4 1 Coal Ila 12 year 6 5 2 Tallisker 10 year 7 6 2 Tallisker Distillers Edition 8 9 1 Lagavullin 16 Year 9 10 Light/Rich (1-10) 11 Delicate/smokey
12 Result

There are two variables Light/Rich and Delicate/Smokey. I want to run a small code so that when people enter a score of 1-10 in Cells B10 and B11 it returns a type of whiskey they might like from column C. So, if I type 2 into B10 and 1 into B11, I should get Laphroig 10 year in B12, the results Cell. I am having trouble combining the two inputs into a index/match or Vlookup equation.

There's two other considerations. It would be really helpful if the code made a nearest match, if there is not an exact match available. One further added complication, there will cases where more than whiskey comes up. Is it possible to list more than one result?

Thanks in advance to any help offered, I appreciate I'm asking alot, but this is beyond my High School Excel Skills.



Excel 2007. I have an array that starts from Column A2 and Goes to Column D5000. The array has new data pasted to it every day for a project. I am trying to Find all of the 3.00 Coupons that are listed in Column B and copy/paste the data from Column C2 into Column F in chronological order by ascending dates. Here are the formulas I have tried.



=IF(ISNUMBER(MATCH(I1,$B$2:$D$5000,0)),"YES","NO") I tried to change this from Yes, No to copy paste. Didn't work.

Array Is :

A2 B2 C2 D2 E2 F2
Date Coupon WA_BPV PPV 3.00 8/9/2010 3.000 2.791 2.779 2.643 3.125 2.762 2.779 1.932 3.250 2.695 2.737 1.969 3.375 2.593 2.230 1.990 3.500 2.532 0.872 2.023 3.625 2.429 0.000 2.068 3.750 1.169 0.000 0.774 3.875 0.774 0.000 1.169 4.000 2.068 0.000 2.429 4.125 2.023 0.000 2.532 4.250 1.990 0.000 2.593 4.375 1.969 0.000 2.695 4.500 1.932 0.000 2.762 8/16/2010 3.000 2.643 2.784 2.791 3.125 2.605 2.784 2.791

I'm trying to use the VLOOKUP function to search for data in six different table arrays. All six tables are in different worksheets, but all have the same format (i.e. four columns). The formula works fine when I'm using only one table array, but when I add another I get a #VALUE error.

So basically, I'd really appreciate some help on how (i.e. what format to write in) to be able to use VLOOKUP to search in multiple table arrays.

Thanks very much in advance!


I'm going to try to explain this the best I can.

I have an xls workbook that I will copy/paste data into daily and wipe out daily for a fresh copy/paste. One of the tabs will be populated with the copy/paste data just in its own format, I'll call that tab AAA.

I have 5 other tabs that will pull from the AAA tab to populate data.

AAA has columns A-P poluated and down to row 261.

The other tabs that pull from AAA have a name assocaition with them in cells. For example, cell A6 is called ABC. It looks up data in AAA for a name ABC and populates the data for that cell accordingly.

In tab AAA, column P is where the name lookups are.

My question is, how do I do this?

A6 is the cell in the other sheet that I want to tell Excel, go to AAA and look for the same name in A6 and populate whatever is in the fourth row. The only problem is, column P is where the data pulls from. How would I make my formula to pull from column P without pulling other data?

Thank you in advance for your help.


Good afternoon all,

Please see below spreadsheet

00200 Bob 00201 Sid 00210 James 00211 Ed 00212 Bruce 00220 Paul 00230 Chris 00700 Claire 00701 Linda 00710 Johnny 00711 Lesley 00712 Therese 00716 Ed 00720 Joan 00730 Luke 01200 Arthur

My Vlookup is =VLOOKUP(D1,B1:C16,2)

The problem is, I want to make multiple vlookups going down the page so it looks like this (depending on what number is input in the column)

00716 Ed 00700 Claire
01200 Arthur

but every time I copy and paste it changes where to search for which is fine but also changes the array location as well so it looks like this


How can i copy and paste down so it reads correctly without changing the array location.


Please bear in mind this is a scaled down example. If it were smaller, I would simply type in the code but it's simply too big.

Can anyone help?

Many thanks in advance,


Hey, I'm looking to sort of intercept a user's paste command and replace it with a 'paste values' or 'match destination formatting' style paste and I'm not sure how to accomplish that with VBA.

The reason for this is that users have to paste a range of data from one place to another, and the target spreadsheet has formatting (banded rows) on it. I have it set up so that conditional formatting applies a format to non-blank cells in even rows, and a different one to cells in odd rows, but pasting screws this up and effectively erases the formatting on the target cells.

If it's not possible to intercept the paste command and replace it with Paste Special - values, or Match Destination Formatting, is there a way to lock the spreadsheet's format / lock the conditional formatting ranges so that they override the incoming data's format?

Thanks for any help!

Hi All,

Is there a way of summing a vlookup within an array.

I have tried this:

=SUM($D78/(VLOOKUP(OFFSET(INDEX('Namesdata'!$A:$A,MATCH($B78,'Namesdata'!$A:$A,0)),0,1,1,22),OFFSET(INDEX(Sheet2!$B:$B,MATCH(VLOOK UP($B78,'Namesdata'!$A:$B,2,FALSE),Sheet2!$B:$B,0)),0,1,COUNTIF(Sheet2!$B:$B,VLOOKUP($B78,'Namesdata'!$A:$B,2,FALSE)),16 ),16,FALSE)))

This produces 2 arrays one is the names that I want to lookup and one is the dynamic ranges that are produced from the names I then want the vlookup to run within each data range and then be sumed.

Any ideas?

Hello, I'm having trouble figuring out how to get paste to work

The Index&Match formula looks to see if there is a value in the cell, if the value is 0 then copy contents from E5 and use same Index&Match formula to paste its value in its place.

Please Login or Register  to view this content.

Thanks for looking!

Atm I am trying to get the following formula to work:

=IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=7,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B3 1:E31),B31:E31,0))),M$2:N$10002,2),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=6,VLOOKUP(MID($ A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),L$2:N$10002,3),IF(INDEX($B$2:$E$2,MATCH(MIN(B3 1:E31),B31:E31,0))=5,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),K$2:N$10002, 4),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=4,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN( B31:E31),B31:E31,0))),J$2:N$10002,5),"ABC"))))

I wont bore you with what i am trying to acheive & why, but the last part of the formula there is a parathesis error apparently. Even though the Brackets all match up imo. ]=

when building IF formulas I usually leave the last error part as "ABC" which i then ichange when entering the next stage.

FYI: =IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=7,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B3 1:E31),B31:E31,0))),M$2:N$10002,2),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=6,VLOOKUP(MID($ A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),L$2:N$10002,3),IF(INDEX($B$2:$E$2,MATCH(MIN(B3 1:E31),B31:E31,0))=5,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),K$2:N$10002, 4),"ABC"))) works. however when changing the "ABC" here, trying to insert:

IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=4,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31 :E31),B31:E31,0))),J$2:N$10002,5),"Check Manual")

I get my error msg,..,. pls someone tell me whats going wrong I Hva ebeen staring at this for hours.


How do you add a vlookup or index match through visual basic based on what is chosen from the combo box? I am sure this has already been asked and answered, but my search button is not working correctly. Sorry.

Also, I would like to do the vlookup or index match from a separate page that the results would go.

I have an "input" page with the combo box (A1) and the cell (B2) for the data for the vlookup is on a "data" page in the workbook.

Thank you all for your help in advance.


I have a sheet which takes data from two different sheets to compare to each other. I use the trim function on one to get the data to match the other. I am left with a 6 digit number that should be the same for both sets of data. Match, Vlookup, and =A3=B3 comes back FALSE. When I evaluate formula I can see A3=123456, but B3="123456". If I copy and paste the cells as values then it works but as soon as I put the formula in it does not.

Any ideas on how to get them to be in the same format?

Hi peeps,

This problem seems to pop up every now and again and i can't seem to quite figure out why. I've done a fair bit of searching on the net too with not much success, actually no success.

I have an excel report which is formatted a certain way (merged cells, font sizes, numbers, highlighting, etc) and when i paste the data into another excel spreadsheet it loses its formatting completely. I've tried all the paste options (paste special values, paste formats, paste column widths the works). Even if i select the format painter tool on a cell in the source file and apply format to a cell in the destination sheet it doesnt take effect.

Strange thing is, this only happens when pasting into certain excel documents. If i paste the source data into a newly opened excel spreadsheet it works perfectly with a simple "Ctrl C" and "Ctrl V"

Anyone else had this issue? Its driving me mad!

Please help !

I have a vlookup formula that doesn't work and I can only think it is a difference in format in the cell that the data is coming from (table array I think).

The table array column used to have a formula in it but I have used paste special to get rid of the formula and replaced with just values.

The formula works if I over-type the cell (with the same number !) so I can only think it is a format thing but the format looks the same in the cells that don't work and those that do work.



using vlookup I want to do the following, I have several table arrays defined in my workbook. Exemple of names: smb, bookings, revenue. I want to build a vlookup that takes it array depending of what is filled in in a cell. Let's say the array name is filled in in cell B1, the lookup value in A1, the vlookup will be: =vlookup(A1;B1;2;false). But this doesn't seem to work as it brings back #N/A.
Any ideas, any other function that can help ?

Thanks in advance,


The problem I'm having is that I can't get either VLOOKUP or INDEX-MATCH queries to work if the cell I'm having it function use to reference the data is a calculation.




J11 = (F18+F19)/F19 [note: F18 and F19 are entered values]

The moment I change J11 to a value (say 1.136), the lookups work. Is there anyway to get around this?



I'm having an issue that I've been working on for about 2 days now, and I've run out of ideas. It's not a very easy issue to explain, but I have tried to do as best as I can in the attached form. (I copied the explanation, although it's not easy without looking at the form itself.) I'm not even sure if Conditional Formatting will work because I cannot enter an array formula into the provided area. I have read that conditional formatting already works on an array assumption, but I haven't been able to validate.

I'm trying to create conditional formatting for the SR### values in the chart above if 1)it's matching category is listed in a special range or 2) its matching category ends in a 3, or 9. For example VLOOKUP(I2,'Raw Data'!Y2:$Z$119,2,0) would return AAAA, which is under the special range (on the coorelation sheet), therefore, I want it to meet the criteria for the conditional format. SR102, or SR109 would also produce like results. I have tried an if statement to compare a return from a Vlookup to a range of cells, in which I can get a true value, but I can't get the correct formatting to stick. I am new to array formulas, and do not fully understand all of the rules yet. I was able to create the solution for #2 in one rule, but want to see if I can combine both of the issues into one Conditional Formatting rule. The numbers (unlike the example in this form) are not sequential, therefore I wasn't sure how INDEX/MATCH would help.

Thanks for any help you can give in advance!

When working with an Excel file, I have inserted a VLOOKUP command which works fine; however, when I copy the command and paste it, using Paste Special - Formula, in the additional cells of the column, I am getting some discrepancies with regard to the formula and its function, primarily in the Table Array entry. The numbers change in Table Array as the column progresses down. I've used simply Copy and Paste and still get the same issue. Is there a way I can copy and paste this VLOOKUP formula and get a consistent formula all the way down the column? Thanks. WDP

If I copy a line of text from someplace (a website, a word document, etc...) and I want to paste it into a cell in excel, is there a way to have to set up the format so that it pastes into the desired format automatically without asking me for PASTE OPTIONS, or having to do it through PASTE SPECIAL everytime. I want to be able to paste something and have it paste in the format I'm already using automatically.