
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 indexmatch combo but this still didnt work.
Any ideas
Thanks in advance
Rich
Similar Excel Video Tutorials
VLOOKUP & INDIRECT & COLUMN functions
 See how to retrieve values from more than one table. See how to populate a template with values using Data Validation, VLOOKUP, INDIRECT, COLUMN, and ...
Zip Code, VLOOKUP & TEXT functions
 See how to use the TEXT and VLOOKUP functions to retrieve Zip Codes with leading zeroes. See how to use Custom Number Formatting (format). ...
Stylistic & Number Formatting
 The Excel Basics Series shows a systematic description of what Excel can do from beginning to end. #12 Video topics: 1)Learn about the Forma ...
Grade Book Based on Percentages
 Use the SUM, VLOOKUP and ROUND functions to create a Grade Book Based on Percentage Scores that will calculate grade for you automatically. See how th ...
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:
=IF(ISNA(VLOOKUP(1ST ARRAY)),IF(ISNA(VLOOKUP(2ND ARRAY)),IF(ISNA(VLOOKUP(3RD ARRAY)),IF(ISNA(VLOOKUP(4TH ARRAY)),IF(ISNA(VLOOKUP(5TH ARRAY)),IF(ISNA(VLOOKUP(6TH ARRAY)),VLOOKUP(7TH ARRAY),VLOOKUP(6TH ARRAY)),VLOOKUP(5TH ARRAY)),VLOOKUP(4TH ARRAY)),VLOOKUP(3RD ARRAY)),VLOOKUP(2ND ARRAY)),VLOOKUP(1ST ARRAY)).
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,
etc....
I have a spreadsheet that I am having supplied by other people.
It has part numbers, that are entered in the following way: 7140501
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
work...
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..
thanks!!
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:
Code:
=D3*(VLOOKUP(E3,Lookups!$A$2:$B$41,2))+(VLOOKUP(Q3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))+(VLOOKUP(R3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))+(VLOOKUP(S3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))+(VLOOKUP(T3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))+(VLOOKUP(U3,INDIRECT(VLOOKUP(E3,Lookups!D2:E17,2)),2))
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??
Hi,
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:
Quote:
=INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2:$C$7),0))
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):
http://www.contextures.com/xlFunctions03.html
Thanks a ton...
Hi,
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 (110)
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 110 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.
Regards,
Tom
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.
=INDEX($B$2:$D$5000,SMALL(IF($B$2:$D$5000=$F$1,ROW($B$2:$D$5000)ROW(B2)+1,ROW(D5000)+1),2),2)
=VLOOKUP(H1,$B$1:$D$5000,MATCH(H1,$B$2:$B$5000,0)*FALSE)
=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
First of all a big thanks to all contributors, I am slowly gaining valuable knowledge from your assistance.
My latest task is to try to populate a register ("Example Register.xls") columns F to N with the source data from a log ("Example Log.xls") columns B to J. The function needs to be based on column A in the log matching column C on the register.
My guess is a lookup of the log value column A row(n) in the register and if found paste the relevant data from columns B to J row(n) into the row(n) containing the lookup match. If no match is found the data should copy/paste into the first available blank row of the register.
I have no idea how to go about this but I can (thanks to previous threads) tackle the "no match" element for pasting to blank rows.
Thank you very much in advance for your continued assistance.
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!
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.
Ty
SP
Hi,
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 AP 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.
=IF(ISERROR(VLOOKUP($A6,'AAA'!$A:D,4,0)),0,VLOOKUP($A6,'AAA'!$A:D,4,0))
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?
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
=VLOOKUP(D2,B2:C17,2)
=VLOOKUP(D3,B3:C18,2)
=VLOOKUP(D4,B4:C19,2)
How can i copy and paste down so it reads correctly without changing the array location.
=VLOOKUP(D1,B1:C16,2)=VLOOKUP(D2,B1:C16,2)
=VLOOKUP(D3,B1:C16,2)
=VLOOKUP(D4,B1:C16,2)
=VLOOKUP(D5,B1:C16,2)
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,
0nyx175
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.
:o
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 nonblank 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!
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 overtype 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.
Thankyou
Antony
The problem I'm having is that I can't get either VLOOKUP or INDEXMATCH queries to work if the cell I'm having it function use to reference the data is a calculation.
ie:
=INDEX(Model!$B$2:$B$269,MATCH(J11,Model!$A$2:$A$269,FALSE),1)
where
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?
Thanks!
Hello,
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,
Ronny
Hello,
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
Hi Everyone,
I have a spreadsheet where I am trying to get a value to show up on a one tab IF the Job Number is present for that day on a different tab. I thought VLOOKUP would work, but it is doing two things I hadn't considered and don't know how to fix. The first is it's coming back with an error message if that Job Number is not present for that day (#N/A). The second thing it is doing is returning a value of 0.00 instead of the actual value of the cell I am looking for. This is for a job costing thing and I am looking for the value by the day if it's applicable and I need it in a number format so I can automatically total the rows/columns. If I copy and paste the section I am working on it is very large. I'm sorry, I don't know how to post these things yet.
This is the spreadsheet that I am pulling the information from. I want it to pull the number from the last column "Time on Job" and paste it into the other tab if the Job Number matches.
Date
Job Number
Job Name
Type
PW Classification
(Mechanic / Helper)
PW
Y/N
Reg
O.T.
D.O.T.
PW Reg
PW O.T.
PW D.O.T.
PW Reg Travel
PW O.T. Travel
PW D.T. Travel
Time on Job
2
Store
Install
No
8.00
1.75
9.75
1
Shop
No
0.25
0.25
3
Work
Repair
0.00
0.00
TOTALS
8.00
2.00
0.00
0.00
0.00
0.00
0.00
0.00
10.00
This is the tab I want the information put into.
Job Number
Job Name
Mon
Tue
Wed
Thur
Fri
Sat
Sun
Total Hours
1
Shop
0.00
0.00
2
Store
0.00
0.00
3
Work
0.00
0.00
4
Money
#N/A
#N/A
5
Car
#N/A
#N/A
The VLOOKUP formula I have to get this information is as follows:
=VLOOKUP(A4,Monday,15,FALSE)
"Monday" is what I named the section of information from the first spreadsheet and the "Time on Job" is the 15th column in that section.
Any ideas what I'm doing wrong, or do you have a better way to get this information?
Thank you!
Cassandra
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.

