Vlookup Returning Incorrect Values For Unfound Match 


Vlookup Returning Incorrect Values For Unfound Match  Excel 
View Answers 
Good afternoon all,
I'm trying to help a colleague automate a daily process he must perform in Excel. I had suspected it to be a simple job for a VLOOKUP formula, however incorrect values are being returned for items without a match.
In the attached example the 'Final! sheet is where he would have to manually enter amounts from an SAP printout into C:C for jobs matching job numbers in B:B. The sheet 'Source Data! is a striped down example of an SAP data dump where he retrieves his information.
The VLOOKUP formula I added to D:D has a couple of issues I could use a hand with.
1. When a value from 'Final!B:B is not found in 'Source Data!B:B, the formula appears to return the value from the previous item.
2. Some values in 'Final!B:B have either an "s" or "ss" appended to what needs to be matched in 'Source Data!B:B. I believe limiting the lookup to the first 8 characters starting from the left would suffice.
Any suggestions?
As always.... thamks for the help!
I'm trying to help a colleague automate a daily process he must perform in Excel. I had suspected it to be a simple job for a VLOOKUP formula, however incorrect values are being returned for items without a match.
In the attached example the 'Final! sheet is where he would have to manually enter amounts from an SAP printout into C:C for jobs matching job numbers in B:B. The sheet 'Source Data! is a striped down example of an SAP data dump where he retrieves his information.
The VLOOKUP formula I added to D:D has a couple of issues I could use a hand with.
1. When a value from 'Final!B:B is not found in 'Source Data!B:B, the formula appears to return the value from the previous item.
2. Some values in 'Final!B:B have either an "s" or "ss" appended to what needs to be matched in 'Source Data!B:B. I believe limiting the lookup to the first 8 characters starting from the left would suffice.
Any suggestions?
As always.... thamks for the help!
Similar Excel Video Tutorials
VLOOKUP 11 Unusual Examples
 See these 11 VLOOKUP tricks:
1.VLOOKUP algorithm
2.VLOOKUP, Named Ranges, Exact Match, COLUMNS function& Data Validation List
3.Com ...
1.VLOOKUP algorithm
2.VLOOKUP, Named Ranges, Exact Match, COLUMNS function& Data Validation List
3.Com ...
VLOOKUP function formula 7 Examples
 Learn about:
1)VLOOKUP to lookup an exact match
2)VLOOKUP to lookup an approximate match
3)VLOOKUP can return a value to a cell
4) ...
1)VLOOKUP to lookup an exact match
2)VLOOKUP to lookup an approximate match
3)VLOOKUP can return a value to a cell
4) ...
INDEX & MATCH Two Lookup Values
 See a formula that can lookup two 2 lookup values using the INDEX & MATCH functions and Concatenated (joined) columns for the lookup value and loo ...
VLOOKUP with Two Lookup Values (IFERROR function also) 2 lookup values
 See how to use the VLOOKUP function when you have two lookup values for each record. Learn how to use the IFERROR function also, and how to join two c ...
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
 This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Get Values from a Chart
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
 This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Replace Formulas with Values (For The Entire Workbook)
 This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
 This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Complete Guide to Printing in Excel Macros  PrintOut Method in Excel
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Filter Data to Show the Top 10 Items from a Data Set in Excel  AutoFilter
 This Excel macro filters data in Excel in order to display the top 10 items from the data set. The macro uses the autof
 This Excel macro filters data in Excel in order to display the top 10 items from the data set. The macro uses the autof
Similar Topics
I have my data in Data Tab as follows:
ID Desc Cat
1 yes decision
2 no final
3 12/31/10 decision
4 misc final
5 1/1/11 decision
6 no review
7 yes final
8 1/5/11 review
9 1/6/11 final
10 1/7/11 review
1 na review
3 part review
7 sold decision
I then I have two lookup tables in Lookup1 and Lookup2 Tabs. Lookup1 has ID values:
ID
1
2
3
4
5
6
7
8
9
10
and Lookup 2 has Cat values:
Cat
Descision
Review
Final
Final Table first column has IDs that populates sorted IDs from Data tab. Column 2 Desc match (lookup) IDs off lookup1 to IDs from Data tab, then match (lookup) Cat from Lookup 2 to Cat in Data tab and when both matched, then it will populate values in Desc in Final Tab. PLease see Final Tab in attached Excel file.
Do I need two vlookup and a match?
Thanks,
ID Desc Cat
1 yes decision
2 no final
3 12/31/10 decision
4 misc final
5 1/1/11 decision
6 no review
7 yes final
8 1/5/11 review
9 1/6/11 final
10 1/7/11 review
1 na review
3 part review
7 sold decision
I then I have two lookup tables in Lookup1 and Lookup2 Tabs. Lookup1 has ID values:
ID
1
2
3
4
5
6
7
8
9
10
and Lookup 2 has Cat values:
Cat
Descision
Review
Final
Final Table first column has IDs that populates sorted IDs from Data tab. Column 2 Desc match (lookup) IDs off lookup1 to IDs from Data tab, then match (lookup) Cat from Lookup 2 to Cat in Data tab and when both matched, then it will populate values in Desc in Final Tab. PLease see Final Tab in attached Excel file.
Do I need two vlookup and a match?
Thanks,
First off, long time viewer and first time poster, so thank you to everyone that posts information here. you have all been a tremendous help.
My current task.
I currently have a task of matching 2 sets of data. The data is simply business names from two different sources. I currently remove all special characters and remove blank spaces. This leaves me with letters only to be matched against one another
As an example, the following could be my first data source:
(data source one is my master data and all subsequent lists are matched against the master)
A1bugerking
B2harveysltd
A3dairyqueen
data source two would be:
B1burgersandkings
B2harveysltd
B3diaryqueen
I am currently running the following formula =IF(ISNA(VLOOKUP(LEFT($A1,1) & "*",DataSource2!$B:$B,1,FALSE)),0,1)
This lets me know if the first character matches. I then run the same formula but to match characters for the second letter of both sets. And another to match the third , and fourth, etc etc.
Currently I use the above formula 20 times, to match up to 20 characters. I then sum the total which gives me the number of characters that match starting from the left until a break appears. As an example the formulas would return the following for the example i provided:
burgerking = 6
harveysltd = 20 (even though the entry is only 10 characters, this formula returns a value of 20. This is actually quite helpful as any entries that have returned a 20 are essentially guaranteed matches)
dairyqueen = 1 (due to the spelling error)
I am thinking there must be a better way.
PS  I am not an advanced user, i have no idea why these formulas work, but they do. I simply found them on this bulletin board. So please keep that in mind when you are judging the mess i created and offering assistance. Thanks
My current task.
I currently have a task of matching 2 sets of data. The data is simply business names from two different sources. I currently remove all special characters and remove blank spaces. This leaves me with letters only to be matched against one another
As an example, the following could be my first data source:
(data source one is my master data and all subsequent lists are matched against the master)
A1bugerking
B2harveysltd
A3dairyqueen
data source two would be:
B1burgersandkings
B2harveysltd
B3diaryqueen
I am currently running the following formula =IF(ISNA(VLOOKUP(LEFT($A1,1) & "*",DataSource2!$B:$B,1,FALSE)),0,1)
This lets me know if the first character matches. I then run the same formula but to match characters for the second letter of both sets. And another to match the third , and fourth, etc etc.
Currently I use the above formula 20 times, to match up to 20 characters. I then sum the total which gives me the number of characters that match starting from the left until a break appears. As an example the formulas would return the following for the example i provided:
burgerking = 6
harveysltd = 20 (even though the entry is only 10 characters, this formula returns a value of 20. This is actually quite helpful as any entries that have returned a 20 are essentially guaranteed matches)
dairyqueen = 1 (due to the spelling error)
I am thinking there must be a better way.
PS  I am not an advanced user, i have no idea why these formulas work, but they do. I simply found them on this bulletin board. So please keep that in mind when you are judging the mess i created and offering assistance. Thanks
Hello,
I am having trouble with this. I have a sheet with values on it. I have another sheet with values on it. I am trying to use a formula, that will will say "complete" if the value from sheet 1 is matched on sheet 2, and to say "incomplete" if nothing is found. I tried this:
=IF(C2=Sheet1!A:A,"complete","incomplete")
The data in C2 happens to match the first cell in column A, so "complete" is returned.
Then I fill down, and it all goes wrong.
The next value (in C3) I have found manually, but my formula returns "incomplete"
I am thinking that VLookup will not work because my data won't be in the first column all the time.
Help!
I am having trouble with this. I have a sheet with values on it. I have another sheet with values on it. I am trying to use a formula, that will will say "complete" if the value from sheet 1 is matched on sheet 2, and to say "incomplete" if nothing is found. I tried this:
=IF(C2=Sheet1!A:A,"complete","incomplete")
The data in C2 happens to match the first cell in column A, so "complete" is returned.
Then I fill down, and it all goes wrong.
The next value (in C3) I have found manually, but my formula returns "incomplete"
I am thinking that VLookup will not work because my data won't be in the first column all the time.
Help!
Using the attached table as an example....
I'm using a VLOOKUP formula to return the values in column J
=VLOOKUP("Bob Smith",A1:J4,10,FALSE)
However, I only want the values returned if there are 3 or more values in columns BH. If there are less than 3, I want "NA" returned.
I've tried a few variations, including putting a COUNTIF formula in another column to determine whether there are 3 or more values, then incorporating that column into the VLOOKUP, but I can't get this to work either.
I was given this formula, which works for the second part of the process (returning the value in J only if there are 3 or more values in BH), but does not incorporate the VLOOKUP.
=IF(COUNTIF(B1:H1,">0")>=3,J2,"NA")
I tried this: =VLOOKUP($A6,A1:J4(IF(COUNTIF(B:H,">0")>=3,J,"NA")),10,FALSE)
But it refers to columns without rows, which I don't think is right, and it returned a Circular Reference error anyway.
Can anyone help?
I'm using a VLOOKUP formula to return the values in column J
=VLOOKUP("Bob Smith",A1:J4,10,FALSE)
However, I only want the values returned if there are 3 or more values in columns BH. If there are less than 3, I want "NA" returned.
I've tried a few variations, including putting a COUNTIF formula in another column to determine whether there are 3 or more values, then incorporating that column into the VLOOKUP, but I can't get this to work either.
I was given this formula, which works for the second part of the process (returning the value in J only if there are 3 or more values in BH), but does not incorporate the VLOOKUP.
=IF(COUNTIF(B1:H1,">0")>=3,J2,"NA")
I tried this: =VLOOKUP($A6,A1:J4(IF(COUNTIF(B:H,">0")>=3,J,"NA")),10,FALSE)
But it refers to columns without rows, which I don't think is right, and it returned a Circular Reference error anyway.
Can anyone help?
Hi,
Here is my situation. I have two sheets. One called "Roster" and one called "final". On the final sheet i have cols for each question on the final exam. I also have a total col which sums up the pts for each question.
On the "roster" sheet. This is kinda like a summary sheet. On this sheet i use a vlookup (shown below) in the cells which are supposed to reference the cells on the "final" sheet for the total pts.
Code:
Here is my problem. Currently the total pts col on the final sheet is 15 cols over. However, if i add another col, which i would do if i added another exam question, the above formula (which is on the "roster" sheet) does not update now to "16"........it is staying at "15" and i dont know why. I thought it would update if i inserted another col on the final sheet.
Can anyone help?
If you need more info just let me know.
Dave
Here is my situation. I have two sheets. One called "Roster" and one called "final". On the final sheet i have cols for each question on the final exam. I also have a total col which sums up the pts for each question.
On the "roster" sheet. This is kinda like a summary sheet. On this sheet i use a vlookup (shown below) in the cells which are supposed to reference the cells on the "final" sheet for the total pts.
Code:
=IF(ISERROR(VLOOKUP(A2,Final!$A$2:$Q$68,15,FALSE)),"NA", VLOOKUP(A2,Final!$A$2:$Q$68,15,FALSE))
Here is my problem. Currently the total pts col on the final sheet is 15 cols over. However, if i add another col, which i would do if i added another exam question, the above formula (which is on the "roster" sheet) does not update now to "16"........it is staying at "15" and i dont know why. I thought it would update if i inserted another col on the final sheet.
Can anyone help?
If you need more info just let me know.
Dave
I have two worksheets, one called "Source", the other "Tracker". The final goal is to update "Tracker" with material from "Source", adding a new row to "Tracker" for each row in "Source" that does not already correspond to a row in "Tracker",
I've mapped the macro out, and here is the essential decision structu
For each row, i, in Source.ColumnX, search Tracker.ColumnA to see if there is a match
If there is a match, go to next row, i+1, in Source.ColumnX
If no match, go to A)
A) on row i, use Source.ColumnY to search Tracker.ColumnB to see if there is a match
If there is, go to B)
If not, go to C)
B) Find last value in Tracker.ColumnB that matches row i of Source.ColumnY
InsertRowsAndFillFormulas (from msvp) for 1 row below that
Paste lookup values in row i from Source.ColumnX and Y into the new row in Tracker.ColumnA and B
Return and search for row i+1 repeat etc.
C) Find correct alphabetical location in Tracker.ColumnB for that value in row i of Source.ColumnY
InsertRowsAndFillFormulas (from msvp) for 1 row
Paste lookup values in row i from Source.ColumnX and Y into the new row in Tracker.ColumnA and B
Return and search for row i+1 repeat etc.
THANK ALL FOR ANY HELP!
I've mapped the macro out, and here is the essential decision structu
For each row, i, in Source.ColumnX, search Tracker.ColumnA to see if there is a match
If there is a match, go to next row, i+1, in Source.ColumnX
If no match, go to A)
A) on row i, use Source.ColumnY to search Tracker.ColumnB to see if there is a match
If there is, go to B)
If not, go to C)
B) Find last value in Tracker.ColumnB that matches row i of Source.ColumnY
InsertRowsAndFillFormulas (from msvp) for 1 row below that
Paste lookup values in row i from Source.ColumnX and Y into the new row in Tracker.ColumnA and B
Return and search for row i+1 repeat etc.
C) Find correct alphabetical location in Tracker.ColumnB for that value in row i of Source.ColumnY
InsertRowsAndFillFormulas (from msvp) for 1 row
Paste lookup values in row i from Source.ColumnX and Y into the new row in Tracker.ColumnA and B
Return and search for row i+1 repeat etc.
THANK ALL FOR ANY HELP!
I'm having big Vlookup issues today as well... I've been banging my head against them for 2 hours now!
I have 3 spreadsheets (1) a list of preferred suppliers on them, (2) with every order we've placed and (3) with all the spend indvidual accounts.
Looking between (2) and (1) I've used
=IF(EXACT(F56,VLOOKUP(F56,'[Confirmed Preferred Vendor List 3rd December 2010 (Final)modded nh.xls]BBES  Final'!$A$2:$A$748,1)),"yes","no")
to tell me if a supplier is on the preferred list. However, I now need to do the same for a more specialist subset on (3). I've used the exact same formula but every single one has returned "no".
I added a line called "test" to sheets (1) and (3) which returned "yes", so I tried overtyping a value I knew was present in both sheets and still got "no".
Suggestions?
I have 3 spreadsheets (1) a list of preferred suppliers on them, (2) with every order we've placed and (3) with all the spend indvidual accounts.
Looking between (2) and (1) I've used
=IF(EXACT(F56,VLOOKUP(F56,'[Confirmed Preferred Vendor List 3rd December 2010 (Final)modded nh.xls]BBES  Final'!$A$2:$A$748,1)),"yes","no")
to tell me if a supplier is on the preferred list. However, I now need to do the same for a more specialist subset on (3). I've used the exact same formula but every single one has returned "no".
I added a line called "test" to sheets (1) and (3) which returned "yes", so I tried overtyping a value I knew was present in both sheets and still got "no".
Suggestions?
Greetings
I have a formula that works nicely below except I have to manually update the rows in order for the formula to work. The reference tab, which is the "FINAL REPORT', last row is not always 109. Once the data feed into the Final Report tab, the last row number varies from 108150. How can adjust my formula to accommodate this function? Thank you in advance. Excellicious.
=INDEX('FINAL REPORT'!C:C,MATCH($A$3,'FINAL REPORT'!$A$2:$A$109,1)+MATCH($B3,INDIRECT(CONCATENATE("'FINAL REPORT'!B",MATCH($A$3,'FINAL REPORT'!$A$2:$A$109,0)+1,":B700")),0),1)
I have a formula that works nicely below except I have to manually update the rows in order for the formula to work. The reference tab, which is the "FINAL REPORT', last row is not always 109. Once the data feed into the Final Report tab, the last row number varies from 108150. How can adjust my formula to accommodate this function? Thank you in advance. Excellicious.
=INDEX('FINAL REPORT'!C:C,MATCH($A$3,'FINAL REPORT'!$A$2:$A$109,1)+MATCH($B3,INDIRECT(CONCATENATE("'FINAL REPORT'!B",MATCH($A$3,'FINAL REPORT'!$A$2:$A$109,0)+1,":B700")),0),1)
Hi all,
I've been using this formula for some time but it has just started returning incorrect results. Its purpose is to give an overview of an employees attendance, drawing data from various spreadsheets by using a reference inserted in cell A6 (the employees ID number). The formula will look for a 1st initial to signify whether an employee was sick, on holiday etc. or, if a numeric vaule is found, was working on any given day. An example of the formula is as follows:
HTML Code:
So, if the employee has been marked as sick on the register an S will be returned (in this case the 1st of the month is column 18), an H for holiday, a W for worked and so on.
The problem I'm getting is that for the current month the reference in cell A6 (51318) is returning values from the row of a different ID (51220). If I extend the extent of the range in the formula from BZ145 to BZ178 (as the table is now larger) for yet another ID (51210). This shouldn't even be causing an issue as the ID I'm looking for is on row 43!
So, as you may see I am rather confused  any help would be gratefully received.
Thanks, Chris
I've been using this formula for some time but it has just started returning incorrect results. Its purpose is to give an overview of an employees attendance, drawing data from various spreadsheets by using a reference inserted in cell A6 (the employees ID number). The formula will look for a 1st initial to signify whether an employee was sick, on holiday etc. or, if a numeric vaule is found, was working on any given day. An example of the formula is as follows:
HTML Code:
=IF((LEFT(VLOOKUP($A$6,'H:\Register 0910\[May 09.xls]Register'!$A$6:$BZ$145,18)))="S","S",IF((LEFT(VLOOKUP($A$6,'H:\\Register 0910\[May 09.xls]Register'!$A$6:$BZ$145,18)))="H","H",IF((LEFT(VLOOKUP($A$6,'H:\Register 0910\[May 09.xls]Register'!$A$6:$BZ$145,18)))="A","A",IF((LEFT(VLOOKUP($A$6,'H:\Register 0910\[May 09.xls]Register'!$A$6:$BZ$145,18)))="N","N",IF((LEFT(VLOOKUP($A$6,'H:\Register 0910\[May 09.xls]Register'!$A$6:$BZ$145,18)))="U","U",IF((LEFT(VLOOKUP($A$6,'H:\Register 0910\[May 09.xls]Register'!$A$6:$BZ$145,18)))="T","T",IF((VLOOKUP($A$6,'H:\Register 0910\[May 09.xls]Register'!$A$6:$BZ$145,18))>=0.1,"W","")))))))
So, if the employee has been marked as sick on the register an S will be returned (in this case the 1st of the month is column 18), an H for holiday, a W for worked and so on.
The problem I'm getting is that for the current month the reference in cell A6 (51318) is returning values from the row of a different ID (51220). If I extend the extent of the range in the formula from BZ145 to BZ178 (as the table is now larger) for yet another ID (51210). This shouldn't even be causing an issue as the ID I'm looking for is on row 43!
So, as you may see I am rather confused  any help would be gratefully received.
Thanks, Chris
Is there a way to use the Right() function with Vlookup on the source data? I know you can use it on data you are returning, but I can't seem to get it to work for source data. Here's what I've tried:
=VLOOKUP(RIGHT(A:A,6),Sheet1!A:D,4,FALSE)
For example: The source data is ABC123456 and I'm trying to match 123456
In the arguments window for "Lookup value" when I type Right(A:A,6) it displays the correct result = "123456", but it only returns #N/A when I try to use it. If I manually delete the ABC from the ABC123456 source data and do a normal Vlookup, it works fine, so it is something about the Right() that it doesn't like.
Thanks /Mikea3
=VLOOKUP(RIGHT(A:A,6),Sheet1!A:D,4,FALSE)
For example: The source data is ABC123456 and I'm trying to match 123456
In the arguments window for "Lookup value" when I type Right(A:A,6) it displays the correct result = "123456", but it only returns #N/A when I try to use it. If I manually delete the ABC from the ABC123456 source data and do a normal Vlookup, it works fine, so it is something about the Right() that it doesn't like.
Thanks /Mikea3
Hello,
I require some assistance here to work with two sets of data source which requires to lookup and match datas between the two source and returns with a single final result.
Referring to the attached Sorting.zip, it contains an Excel file with two worksheets as the data sources, Source_local and Source_vendor. I am require to lookup and match the data sources from either one of the column, VIN or ENGINE_NO in Source_local and match it to either column, CHASSIS NO or ENGINE NO in Source_vendor. Upon finding the matching data, it is require to return a matching result from column AREA CODE in Source_vendor. The result will be display at any available column in Source_local.
Based on above descriptions I wonder which will be a better formula/function to achieve the results. It is possible for me to use VLOOKUP + MATCH for the above?
Thanks
Law
I require some assistance here to work with two sets of data source which requires to lookup and match datas between the two source and returns with a single final result.
Referring to the attached Sorting.zip, it contains an Excel file with two worksheets as the data sources, Source_local and Source_vendor. I am require to lookup and match the data sources from either one of the column, VIN or ENGINE_NO in Source_local and match it to either column, CHASSIS NO or ENGINE NO in Source_vendor. Upon finding the matching data, it is require to return a matching result from column AREA CODE in Source_vendor. The result will be display at any available column in Source_local.
Based on above descriptions I wonder which will be a better formula/function to achieve the results. It is possible for me to use VLOOKUP + MATCH for the above?
Thanks
Law
Guys,
I have an issue whereby vlookup is returning incorrect data.
I have a named data range on worksheet "prices" that is called from a drop down box on another worksheet, so you get a drop down list with all of the item names.
Next to the item names on the first page is the price of that particular item.
Next to the drop down box on the second worksheet is a vlookup that looks at the value of the drop down box, and takes the value in column 2 of the afordmentioned sheet.
The vlookup returns the wrong value in some cases. My understanding is that with:
=VLOOKUP(A37,Prices!H14:I53,2)
Excel SHOULD look at whats in A37. It then looks on the Prices worksheet in cells H14:I53. When it finds what it found in A37 within that range, it returns the data in column I (column 2) that is next to the matched item.
However, if you look at the spreadsheet, try selecting "tungsten carbide Armor" and youll see its actually returning the price for "Titanium Diborate Armor" if you refer to the "prices" worksheet.
What is going on, can anyone help? Theres quite a few doing this!
Spreadsheet attached, apologies if its agianst the rules. Theres nothing important or sensitive in there.
I have an issue whereby vlookup is returning incorrect data.
I have a named data range on worksheet "prices" that is called from a drop down box on another worksheet, so you get a drop down list with all of the item names.
Next to the item names on the first page is the price of that particular item.
Next to the drop down box on the second worksheet is a vlookup that looks at the value of the drop down box, and takes the value in column 2 of the afordmentioned sheet.
The vlookup returns the wrong value in some cases. My understanding is that with:
=VLOOKUP(A37,Prices!H14:I53,2)
Excel SHOULD look at whats in A37. It then looks on the Prices worksheet in cells H14:I53. When it finds what it found in A37 within that range, it returns the data in column I (column 2) that is next to the matched item.
However, if you look at the spreadsheet, try selecting "tungsten carbide Armor" and youll see its actually returning the price for "Titanium Diborate Armor" if you refer to the "prices" worksheet.
What is going on, can anyone help? Theres quite a few doing this!
Spreadsheet attached, apologies if its agianst the rules. Theres nothing important or sensitive in there.
Hi Everyone,
I have two sheets currently. One is a Data Dump (on the bottom). The other is "sheet4" on the top where I just need to be able to match the column with the row (Vlookup/HLookup). Is there a vlookup/hlookup combo formula that can get me to the answer I want? Note that my Rows consist of dropdown menus/dates that change accordingly.. Please see below.
Sorry for entering it here instead of a real worksheet. I can't seem to attach a file small enough to be able to be posted on this website!!
Thank you very much!!!
SHEET 4
ENTER DATE ENTER DATE Name of Manager May11 Jun11 Total Plan (9/30/90) Total Fund Benchmark Looking to be able to go to "Sheet ONE DATA DUMP below and get a match of 1.3. How do I get a vlookup and hlookup formula to get this? Looking to be able to go to "Sheet ONE DATA and get a match of 1.5. How do I get a vlookup and hlookup formula to get this?
SHEET  ONE DATA DUMP
May11 Jun11 Total Plan (9/30/90) 1.1 1.0 Total Fund Benchmark 1.3 1.5 Mstar Moderate Allocation Fund 0.8 1.4 CPI Plus 5% 0.7 0.2
I have two sheets currently. One is a Data Dump (on the bottom). The other is "sheet4" on the top where I just need to be able to match the column with the row (Vlookup/HLookup). Is there a vlookup/hlookup combo formula that can get me to the answer I want? Note that my Rows consist of dropdown menus/dates that change accordingly.. Please see below.
Sorry for entering it here instead of a real worksheet. I can't seem to attach a file small enough to be able to be posted on this website!!
Thank you very much!!!
SHEET 4
ENTER DATE ENTER DATE Name of Manager May11 Jun11 Total Plan (9/30/90) Total Fund Benchmark Looking to be able to go to "Sheet ONE DATA DUMP below and get a match of 1.3. How do I get a vlookup and hlookup formula to get this? Looking to be able to go to "Sheet ONE DATA and get a match of 1.5. How do I get a vlookup and hlookup formula to get this?
SHEET  ONE DATA DUMP
May11 Jun11 Total Plan (9/30/90) 1.1 1.0 Total Fund Benchmark 1.3 1.5 Mstar Moderate Allocation Fund 0.8 1.4 CPI Plus 5% 0.7 0.2
I am using concatenate to create a list of bottles based on other data, so that each cell contains a different combination of bottles.
The problem is that the list appears but there is also FALSE for every bottle that doesn't relate to that cell.
Is there a way to create a formula such that nothing appears if it is not true?
And is there a shorter way of writing what I am after, I can't quite finish the formula becaise it is so long.
This is what I am using:
=CONCATENATE(IF(ISNUMBER(MATCH("*O1*",C3,0)), VLOOKUP("O1",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*O2*",C3,0)), VLOOKUP("O2",$A$10:$D$19,4,0)), ", ", IF(ISNUMBER(MATCH("*O3*",C3,0)), VLOOKUP("O3",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*O4*",C3,0)), VLOOKUP("O4",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*O5*",C3,0)), VLOOKUP("O5",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*O6*",C3,0)), VLOOKUP("O6",$A$10:$D$19, 4,0)), ", ", IF(ISNUMBER(MATCH("*O7*",C3,0)), VLOOKUP("O7",$A$10:$D$19, 4,0)), ", ", IF(ISNUMBER(MATCH("*O8*",C3,0)), VLOOKUP("O8",A10:D19, 4,0)), ", ", IF(ISNUMBER(MATCH("*O9*",C3,0)), VLOOKUP("O9",$A$10:$D$19, 4," ")), ", ", IF(ISNUMBER(MATCH("*OS*",C3,0)), VLOOKUP("OS",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*I1",E3,0)), VLOOKUP("I1",$A$21:$D$26, 4,0)), ", ",IF(ISNUMBER(MATCH("*I2*",E3,0)), VLOOKUP("I2",$A$21:$D$26,4,0)))
As I am here, does anyone know how to stop the formula text appearing? Cause when i select this cell it takes over half the screen as it is so long.
Thanks for listening
The problem is that the list appears but there is also FALSE for every bottle that doesn't relate to that cell.
Is there a way to create a formula such that nothing appears if it is not true?
And is there a shorter way of writing what I am after, I can't quite finish the formula becaise it is so long.
This is what I am using:
=CONCATENATE(IF(ISNUMBER(MATCH("*O1*",C3,0)), VLOOKUP("O1",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*O2*",C3,0)), VLOOKUP("O2",$A$10:$D$19,4,0)), ", ", IF(ISNUMBER(MATCH("*O3*",C3,0)), VLOOKUP("O3",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*O4*",C3,0)), VLOOKUP("O4",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*O5*",C3,0)), VLOOKUP("O5",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*O6*",C3,0)), VLOOKUP("O6",$A$10:$D$19, 4,0)), ", ", IF(ISNUMBER(MATCH("*O7*",C3,0)), VLOOKUP("O7",$A$10:$D$19, 4,0)), ", ", IF(ISNUMBER(MATCH("*O8*",C3,0)), VLOOKUP("O8",A10:D19, 4,0)), ", ", IF(ISNUMBER(MATCH("*O9*",C3,0)), VLOOKUP("O9",$A$10:$D$19, 4," ")), ", ", IF(ISNUMBER(MATCH("*OS*",C3,0)), VLOOKUP("OS",$A$10:$D$19, 4,0)), ", ",IF(ISNUMBER(MATCH("*I1",E3,0)), VLOOKUP("I1",$A$21:$D$26, 4,0)), ", ",IF(ISNUMBER(MATCH("*I2*",E3,0)), VLOOKUP("I2",$A$21:$D$26,4,0)))
As I am here, does anyone know how to stop the formula text appearing? Cause when i select this cell it takes over half the screen as it is so long.
Thanks for listening
I have my data in Data Tab as follows:
ID Desc Cat
1 yes decision
2 no final
3 12/31/10 decision
4 misc final
5 1/1/11 decision
6 no review
7 yes final
8 1/5/11 review
9 1/6/11 final
10 1/7/11 review
1 na review
3 part review
7 sold decision
I then I have two lookup tables in Lookup1 and Lookup2 Tabs. Lookup1 has ID values:
ID
1
2
3
4
5
6
7
8
9
10
and Lookup 2 has Cat values:
Cat
Descision
Review
Final
Final Table first column has IDs that populates sorted IDs from Data tab. Column 2 Desc match (lookup) IDs off lookup1 to IDs from Data tab, then match (lookup) Cat from Lookup 2 to Cat in Data tab and when both matched, then it will populate values in Desc in Final Tab. Final Tab:
ID Desc
1 yes
1 na
2 no
3 12/31/10
3 part
4 misc
5 1/1/11 e
6 no
7 yes
7 sold
8 1/5/11
9 1/6/11
10 1/7/11
Thanks,
ID Desc Cat
1 yes decision
2 no final
3 12/31/10 decision
4 misc final
5 1/1/11 decision
6 no review
7 yes final
8 1/5/11 review
9 1/6/11 final
10 1/7/11 review
1 na review
3 part review
7 sold decision
I then I have two lookup tables in Lookup1 and Lookup2 Tabs. Lookup1 has ID values:
ID
1
2
3
4
5
6
7
8
9
10
and Lookup 2 has Cat values:
Cat
Descision
Review
Final
Final Table first column has IDs that populates sorted IDs from Data tab. Column 2 Desc match (lookup) IDs off lookup1 to IDs from Data tab, then match (lookup) Cat from Lookup 2 to Cat in Data tab and when both matched, then it will populate values in Desc in Final Tab. Final Tab:
ID Desc
1 yes
1 na
2 no
3 12/31/10
3 part
4 misc
5 1/1/11 e
6 no
7 yes
7 sold
8 1/5/11
9 1/6/11
10 1/7/11
Thanks,
Greetings!
I'm having an issue with VLookup, i've attempted to search the forums for the solution but to be honest i wouldnt even know how to keyword it.
My issue is that I have a data source, column A has an I.D number ( starting from 1 all the way up to 900), but where some records have been deleted there are now gaps in the I.D numbering (.. 47, 48, 49, 52, 55 etc.).
Now, When i'm trying to use the ID numbers given to me via a seperate report and attempt to use Vlookup to gain the data from the data source it is picking up the data correctly.. until it reaches an ID number that exists on that sheet but not the data source. What happens then is that it uses the previous entries data rather than declaring that cell as blank.
I've attached an example outlining what i mean: the example.xls file is the information with the VLookup information, and the data source.xls is, well, the data source.
Please note that ID number 281 tells me it belongs to Person 130, when the data source tells me Person 130's ID number is 280.
Is there a way for the formula to give back a blank entry if it cannot find a number within the data source?
I'm having an issue with VLookup, i've attempted to search the forums for the solution but to be honest i wouldnt even know how to keyword it.
My issue is that I have a data source, column A has an I.D number ( starting from 1 all the way up to 900), but where some records have been deleted there are now gaps in the I.D numbering (.. 47, 48, 49, 52, 55 etc.).
Now, When i'm trying to use the ID numbers given to me via a seperate report and attempt to use Vlookup to gain the data from the data source it is picking up the data correctly.. until it reaches an ID number that exists on that sheet but not the data source. What happens then is that it uses the previous entries data rather than declaring that cell as blank.
I've attached an example outlining what i mean: the example.xls file is the information with the VLookup information, and the data source.xls is, well, the data source.
Please note that ID number 281 tells me it belongs to Person 130, when the data source tells me Person 130's ID number is 280.
Is there a way for the formula to give back a blank entry if it cannot find a number within the data source?
Scoured forum and couldn't find answer to this.
Sheet 1 ("Dump") is a raw dump of data relating to survey results from a number of different cities. I want to read through this dump, isolate any rows where Location="Glasgow" and copy them across into a new list on an existing sheet ("Report")  replacing any existing data on that sheet.
Although I can manually delete "Report" cells, filter "Dump" sheet and then copy and paste I am looking for some way to do this automatically as the data dump is updated daily by a manual copy and paste operation. I cannot filter the data at source as i will require comparison to other locations elsewhere in the workbook and need all the data present in "Dump"
Tried a straight =IF(DUMP!A1="Glasgow","DUMP!A1","") but this leaves obvious gaps that mess up later calculations based on the data.
Have tried VLOOKUP but this obviously only returns first survey. Do not see how INDEX/MATCH can perform this either.
Can anyone point me in right direction here pls?
Sheet 1 ("Dump") is a raw dump of data relating to survey results from a number of different cities. I want to read through this dump, isolate any rows where Location="Glasgow" and copy them across into a new list on an existing sheet ("Report")  replacing any existing data on that sheet.
Although I can manually delete "Report" cells, filter "Dump" sheet and then copy and paste I am looking for some way to do this automatically as the data dump is updated daily by a manual copy and paste operation. I cannot filter the data at source as i will require comparison to other locations elsewhere in the workbook and need all the data present in "Dump"
Tried a straight =IF(DUMP!A1="Glasgow","DUMP!A1","") but this leaves obvious gaps that mess up later calculations based on the data.
Have tried VLOOKUP but this obviously only returns first survey. Do not see how INDEX/MATCH can perform this either.
Can anyone point me in right direction here pls?
I know I have done this formula before, but the syntax escapes me presently and was hoping someone here would be able to job the old memory.
I am trying to use vlookup to "cleanup" a listing of data so that only particular rows are presented. In my workbook I have a "Raw Data" sheet with all the information and a "Complete" sheet which would be populated by vlookup strings.
The issue I am coming across is on the second row of the "Complete" sheet. The first vlookup is simple and does of course pull the first item in the Raw Data that I want. However, without the proper addition to my formula, the second row etc. also pulls the first item in the Raw Data. I seem to recall having used a variation of a vlookup formula in the past to solve this problem. Something similar to =if(iserror(vlookup(.... except I do not believe the second statement was iserror and I am certain that somewhere in the formula there was a +1.
Basically, onthe "Complete" sheet cell A1 would read =vlookup("y",'Raw Data'!$A$1:$C$8000,3,false)
cell A2 would need to reference the result of A1 and if it matched, it would then pull the next data from the Raw data sheet.
Any help would be appreciated.
I am trying to use vlookup to "cleanup" a listing of data so that only particular rows are presented. In my workbook I have a "Raw Data" sheet with all the information and a "Complete" sheet which would be populated by vlookup strings.
The issue I am coming across is on the second row of the "Complete" sheet. The first vlookup is simple and does of course pull the first item in the Raw Data that I want. However, without the proper addition to my formula, the second row etc. also pulls the first item in the Raw Data. I seem to recall having used a variation of a vlookup formula in the past to solve this problem. Something similar to =if(iserror(vlookup(.... except I do not believe the second statement was iserror and I am certain that somewhere in the formula there was a +1.
Basically, onthe "Complete" sheet cell A1 would read =vlookup("y",'Raw Data'!$A$1:$C$8000,3,false)
cell A2 would need to reference the result of A1 and if it matched, it would then pull the next data from the Raw data sheet.
Any help would be appreciated.
Hello from Canada during our beautiful twoweek summer! Thank you in advance for any suggestions you might offer.
I have a large dataset for which I want to combine data by Latitude and Longitude. I have included on a single sheet data showing how well the plants are growing in specific locations, and data showing how much rain has fallen. All data are annual, so I want to address that variable also, but I have figured out a way to do it just by sorting on that variable.
The problem is that the Latitudes and Longitudes do not match exactly.
I tried using vlookup to match the precipitation values by creating both source and lookup data using a pipe =N3&""&O3 and the standard vlookup formula, but received wrong information. All lookup data are sorted ascending.
Also, I checked and rechecked formatting even by paste special product*1 and using text to columns.
This is a challenging puzzle, master points to anybody who can figure it out! VBA is something I'm comfortable with, if that seems like a superior way to handle this.
The file is too large to upload here, so I have posted it he
http://globalcarbonindex.org/vlookup_test_ozgrid.xlsx
Thanks!
cc
I have a large dataset for which I want to combine data by Latitude and Longitude. I have included on a single sheet data showing how well the plants are growing in specific locations, and data showing how much rain has fallen. All data are annual, so I want to address that variable also, but I have figured out a way to do it just by sorting on that variable.
The problem is that the Latitudes and Longitudes do not match exactly.
I tried using vlookup to match the precipitation values by creating both source and lookup data using a pipe =N3&""&O3 and the standard vlookup formula, but received wrong information. All lookup data are sorted ascending.
Also, I checked and rechecked formatting even by paste special product*1 and using text to columns.
This is a challenging puzzle, master points to anybody who can figure it out! VBA is something I'm comfortable with, if that seems like a superior way to handle this.
The file is too large to upload here, so I have posted it he
http://globalcarbonindex.org/vlookup_test_ozgrid.xlsx
Thanks!
cc
This is a very long equation I'm working on. The basic premise of the equation is using IF and AND to determine what equation to calculate the price should be used:
IF(AND(this is/isn't true, this is true),then do this,IF(AND(this is/isn't true, this is true),then do this,repeat logic
This is approximately 6 IF statements deep
I will break the statement down so it is easier to follow below at the bottom:
Full equation
[i]=IF(A12="","",(IF(AND(G12="",VLOOKUP(B12,$L$3:$P$302,5,0)="Profile"),VLOOKUP($B12,$L$3:$P$302,2,0)*$F12*$S$17+VLOOKUP($C12,$R$2 1:$S$28,2,0),(IF(AND(G12<>"",VLOOKUP(B12,$L$3:$P$302,5,0)="Profile"),VLOOKUP($B12,$L$3:$P$302,2,0)*($F12+0.6)*$S$17+VLOOKUP($G1 2,$R$44:$S$47,2,0)+VLOOKUP($C12,$R$21:$S$28,2,0),(IF(AND(G12="",VLOOKUP($B12,$L$3:$P$302,3,0)="Metre"),VLOOKUP($B12,$L$3:$P$302,2,0)*$F12*$S$18+VLOOKUP($C12,$R$21 :$S$28,2,0),(IF(AND($G12<>"",(VLOOKUP($B12,$L$3:$P$302,3,0)="Metre"),VLOOKUP($B12,$L$3:$P$302,2,0)*($F12+0.6)*$S$18+VLOOKUP($G1 2,$R$44:$S$47,2,0)+VLOOKUP($C12,$R$21:$S$28,2,0),(IF(AND($F12="",VLOOKUP(B12,$L$3:$P$302,3,0)=OR("Pair","Piece")),VLOOKUP($B12,$L$3:$P$302,2,0)*$S$18,"Check values"))))))))))))
Broken down equation
=IF(A12="","",
(IF(AND(G12="",VLOOKUP(B12,$L$3:$P$302,5,0)="Profile"),
VLOOKUP($B12,$L$3:$P$302,2,0)*$F12*$S$17+
VLOOKUP($C12,$R$21:$S$28,2,0),
(IF(AND(G12<>"",VLOOKUP(B12,$L$3:$P$302,5,0)="Profile"),
VLOOKUP($B12,$L$3:$P$302,2,0)*($F12+0.6)*$S$17+
VLOOKUP($G12,$R$44:$S$47,2,0)+
VLOOKUP($C12,$R$21:$S$28,2,0),
(IF(AND(G12="",VLOOKUP($B12,$L$3:$P$302,3,0)="Metre"),
VLOOKUP($B12,$L$3:$P$302,2,0)*$F12*$S$18+
VLOOKUP($C12,$R$21:$S$28,2,0),
(IF(AND($G12<>"",(VLOOKUP($B12,$L$3:$P$302,3,0)="Metre"),
VLOOKUP($B12,$L$3:$P$302,2,0)*($F12+0.6)*$S$18+
VLOOKUP($G12,$R$44:$S$47,2,0)+
VLOOKUP($C12,$R$21:$S$28,2,0),
(IF(AND($F12="", VLOOKUP (B12,$L$3:$P$302,3,0)=OR("Pair","Piece")),
VLOOKUP($B12,$L$3:$P$302,2,0)*$S$18,"Check values"))))))))))))
The equation throws up an error at the point where the red VLOOKUP is. However all table references are correct, as are the potential values "Pair" and "Piece" it is looking for, as is column '3' to look for them in. Can anyone see what the problem is here? Is there a max number for VLOOKUP you can use, have I hit some kind of limit?
Really stuck here and believe it's an equation / limit issue. Not an incorrect reference.
This equation refers to column E. However you will have to manually copy and paste it into excel as I cannot save the Excel file with this incorrect equation.
It's driving me crazy, could really do with a quick answer!
IF(AND(this is/isn't true, this is true),then do this,IF(AND(this is/isn't true, this is true),then do this,repeat logic
This is approximately 6 IF statements deep
I will break the statement down so it is easier to follow below at the bottom:
Full equation
[i]=IF(A12="","",(IF(AND(G12="",VLOOKUP(B12,$L$3:$P$302,5,0)="Profile"),VLOOKUP($B12,$L$3:$P$302,2,0)*$F12*$S$17+VLOOKUP($C12,$R$2 1:$S$28,2,0),(IF(AND(G12<>"",VLOOKUP(B12,$L$3:$P$302,5,0)="Profile"),VLOOKUP($B12,$L$3:$P$302,2,0)*($F12+0.6)*$S$17+VLOOKUP($G1 2,$R$44:$S$47,2,0)+VLOOKUP($C12,$R$21:$S$28,2,0),(IF(AND(G12="",VLOOKUP($B12,$L$3:$P$302,3,0)="Metre"),VLOOKUP($B12,$L$3:$P$302,2,0)*$F12*$S$18+VLOOKUP($C12,$R$21 :$S$28,2,0),(IF(AND($G12<>"",(VLOOKUP($B12,$L$3:$P$302,3,0)="Metre"),VLOOKUP($B12,$L$3:$P$302,2,0)*($F12+0.6)*$S$18+VLOOKUP($G1 2,$R$44:$S$47,2,0)+VLOOKUP($C12,$R$21:$S$28,2,0),(IF(AND($F12="",VLOOKUP(B12,$L$3:$P$302,3,0)=OR("Pair","Piece")),VLOOKUP($B12,$L$3:$P$302,2,0)*$S$18,"Check values"))))))))))))
Broken down equation
=IF(A12="","",
(IF(AND(G12="",VLOOKUP(B12,$L$3:$P$302,5,0)="Profile"),
VLOOKUP($B12,$L$3:$P$302,2,0)*$F12*$S$17+
VLOOKUP($C12,$R$21:$S$28,2,0),
(IF(AND(G12<>"",VLOOKUP(B12,$L$3:$P$302,5,0)="Profile"),
VLOOKUP($B12,$L$3:$P$302,2,0)*($F12+0.6)*$S$17+
VLOOKUP($G12,$R$44:$S$47,2,0)+
VLOOKUP($C12,$R$21:$S$28,2,0),
(IF(AND(G12="",VLOOKUP($B12,$L$3:$P$302,3,0)="Metre"),
VLOOKUP($B12,$L$3:$P$302,2,0)*$F12*$S$18+
VLOOKUP($C12,$R$21:$S$28,2,0),
(IF(AND($G12<>"",(VLOOKUP($B12,$L$3:$P$302,3,0)="Metre"),
VLOOKUP($B12,$L$3:$P$302,2,0)*($F12+0.6)*$S$18+
VLOOKUP($G12,$R$44:$S$47,2,0)+
VLOOKUP($C12,$R$21:$S$28,2,0),
(IF(AND($F12="", VLOOKUP (B12,$L$3:$P$302,3,0)=OR("Pair","Piece")),
VLOOKUP($B12,$L$3:$P$302,2,0)*$S$18,"Check values"))))))))))))
The equation throws up an error at the point where the red VLOOKUP is. However all table references are correct, as are the potential values "Pair" and "Piece" it is looking for, as is column '3' to look for them in. Can anyone see what the problem is here? Is there a max number for VLOOKUP you can use, have I hit some kind of limit?
Really stuck here and believe it's an equation / limit issue. Not an incorrect reference.
This equation refers to column E. However you will have to manually copy and paste it into excel as I cannot save the Excel file with this incorrect equation.
It's driving me crazy, could really do with a quick answer!
Hi Everyone,
This is my first post here, and I have used this board before as reference but never got around to posting.Example Data.xlsx
This is a probably a simple question to answer, but here's my situation.
In Sheet "Data" I have a list of item numbers from Column A2 down, A1 is titled "Item Numbers."
In Sheet "Master" I have a whole spread of data that I need to bring over to Sheet "Data". "Data is about 206 rows down and "Master" is approx 3,000. The data in master begins on A2, A1 is titled Item Numbers.
What I need to do is match the items numbers in Sheet "Data" to the item numbers in "Master" while bringing over all the data in the columns next to the item numbers in "Master". The data that I am trying to match and bring over to the "Data" sheet goes til column CM in "Master."
I'm thinking it'll need the Index formula since my basis vlookup wasn't working, but I figured I'd finally get my first post out there and ask the experts. Thank you all in advance.
This is my first post here, and I have used this board before as reference but never got around to posting.Example Data.xlsx
This is a probably a simple question to answer, but here's my situation.
In Sheet "Data" I have a list of item numbers from Column A2 down, A1 is titled "Item Numbers."
In Sheet "Master" I have a whole spread of data that I need to bring over to Sheet "Data". "Data is about 206 rows down and "Master" is approx 3,000. The data in master begins on A2, A1 is titled Item Numbers.
What I need to do is match the items numbers in Sheet "Data" to the item numbers in "Master" while bringing over all the data in the columns next to the item numbers in "Master". The data that I am trying to match and bring over to the "Data" sheet goes til column CM in "Master."
I'm thinking it'll need the Index formula since my basis vlookup wasn't working, but I figured I'd finally get my first post out there and ask the experts. Thank you all in advance.
Hi all,
I was wondering how to make the following: get a list where each Number from the "source file.xls" is assigned to a Group like in "final list.xls" through "conversion table.xls".
In the "source file.xls" we have apart from Number, the Series and Category columns. I need to compare both Series and Category to the same from "conversion table.xls" and in the third workbook output the matching Number from "source file.xls" and Group from "conversion table.xls" according to matching algorithm.
I am attaching the files:
source file.xls
conversion table.xls
final list.xls
All three should be different workbooks and the first two will need to be closed. Also very important is the source file can have many names so it would be great to implement an open dialog box to load the data from "source file.xls". All data is in text format.
Thanks for any help or advice.
Cheers.
I was wondering how to make the following: get a list where each Number from the "source file.xls" is assigned to a Group like in "final list.xls" through "conversion table.xls".
In the "source file.xls" we have apart from Number, the Series and Category columns. I need to compare both Series and Category to the same from "conversion table.xls" and in the third workbook output the matching Number from "source file.xls" and Group from "conversion table.xls" according to matching algorithm.
I am attaching the files:
source file.xls
conversion table.xls
final list.xls
All three should be different workbooks and the first two will need to be closed. Also very important is the source file can have many names so it would be great to implement an open dialog box to load the data from "source file.xls". All data is in text format.
Thanks for any help or advice.
Cheers.
My formula is returning an #N/A when there is no match from my data source file, meaning I have an account name on my main sheet where the formula resides but on the data source sheet, this account name does not exist, therefore the formula returns the #N/A, how can I get the formula to return a Zero, I played around with an IF(ISNA) and an ISERROR, but could not get to work, I got the ISERROR to identify accounts not present on the data source sheet, but would rather just have the formula return a Zero, any suggestions?
=INDEX('data source sheet'!$B$1:$C$500,MATCH(A164,'data source sheet'!$A$1:$A$500,0),MATCH($C$1,'data source sheet'!$B$1:$C$1,0))
=INDEX('data source sheet'!$B$1:$C$500,MATCH(A164,'data source sheet'!$A$1:$A$500,0),MATCH($C$1,'data source sheet'!$B$1:$C$1,0))
Hello group,
I've worked with excel for many years, but I could use some help throwing
this together. The background:
I have two sheets within the same workbook. One sheet has check numbers and
values that have been issued by the company. The other sheet has that same
information, but only what has been reported back to us by the bank. I
working on streamlining the comparison between the two sheets to more easily
see what checks are outstanding (have not been cashed at the bank). Also,
if the check has been cashed, I want to compare the value that the bank
recorded with the value that our company recorded to insure that they match
up.
My problem is that the table is going to grow as the year goes on and I'm
not sure how to get the VLOOKUP table array to grow with it. In the
following formula, F5 is the cell that contains the check number on our
company detail sheet, Bank DetailB6:C17206 is the the table array where the
banks check number and value is stored:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No
Match","Match"))
Is there a way to use the indirect function and/or the Row function to
update the row reference to 17206 (the footer row in my bank detail sheet?
This way, as more bank detail is added, that row reference will remain at
the bottom of the list.
I know the syntax doesn't work, but I would like it to do this:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
Detail'!C17206),1,FALSE)),"No Match","Match"))
Maybe there's a better way to go about this. I'm open to suggestions if the
community has any. Right now, each sheet has the following columns:
1.) Check Number
2.) Value
3.) Match?  if the check number is found on the other sheet, "Match" is
entered in to the cell, otherwise "No Match" is entered
4.) Amount of nonmatching Checks  If Cell "Match?" = "No Match", Value
amount is entered into the cell
5.) Amount of Matching Check from other sheet if Values differ  If a check
number match is found on the other sheet, this cell compares the two values
and return the other sheets value only if the two values don't match.
Any help/comments/suggestions will be appreciated.
Michael
I've worked with excel for many years, but I could use some help throwing
this together. The background:
I have two sheets within the same workbook. One sheet has check numbers and
values that have been issued by the company. The other sheet has that same
information, but only what has been reported back to us by the bank. I
working on streamlining the comparison between the two sheets to more easily
see what checks are outstanding (have not been cashed at the bank). Also,
if the check has been cashed, I want to compare the value that the bank
recorded with the value that our company recorded to insure that they match
up.
My problem is that the table is going to grow as the year goes on and I'm
not sure how to get the VLOOKUP table array to grow with it. In the
following formula, F5 is the cell that contains the check number on our
company detail sheet, Bank DetailB6:C17206 is the the table array where the
banks check number and value is stored:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$17206,1,FALSE)),"No
Match","Match"))
Is there a way to use the indirect function and/or the Row function to
update the row reference to 17206 (the footer row in my bank detail sheet?
This way, as more bank detail is added, that row reference will remain at
the bottom of the list.
I know the syntax doesn't work, but I would like it to do this:
=IF(F5="","",IF(ISNA(VLOOKUP(F5,'Bank Detail'!$B$6:$C$ROW('Bank
Detail'!C17206),1,FALSE)),"No Match","Match"))
Maybe there's a better way to go about this. I'm open to suggestions if the
community has any. Right now, each sheet has the following columns:
1.) Check Number
2.) Value
3.) Match?  if the check number is found on the other sheet, "Match" is
entered in to the cell, otherwise "No Match" is entered
4.) Amount of nonmatching Checks  If Cell "Match?" = "No Match", Value
amount is entered into the cell
5.) Amount of Matching Check from other sheet if Values differ  If a check
number match is found on the other sheet, this cell compares the two values
and return the other sheets value only if the two values don't match.
Any help/comments/suggestions will be appreciated.
Michael
This formula below does an ok job but but it doesn't have the ability to identify the las row of the Final Report tab. I would manually have to adjust the "119", which defines the last row, in order for it to work. I did try to define the name range=OFFSET(FINAL REPORT!$A$1,0,0,COUNTA(FINAL REPORT!$A$2 REPORT!$A:$A),1) but no luck.
=INDEX('FINAL REPORT'!C:C,MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,1)+MATCH($B3,INDIRECT(CONCATENATE("'FINAL REPORT'!B",MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,0)+1,":B700")),0),1)
TIA,
excellicious
=INDEX('FINAL REPORT'!C:C,MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,1)+MATCH($B3,INDIRECT(CONCATENATE("'FINAL REPORT'!B",MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,0)+1,":B700")),0),1)
TIA,
excellicious