|
Vlookup problem
I have data that needs to be transferred over from one month to the next - 2
seperate workbooks. I wanted to use a vlookup formula to carry over the data: VLOOKUP(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!$A:$F,-6,0) my problem is the field that needs to be populated is to the left of the unique id field and using a negative sign doesnt work. Also moving the columns isn't feasable. Also, if nothing is in the field I want it to leave the field blank instead of putting #N/A can I accomplish this with a different function? |
Vlookup problem
Belinda,
You can't do left lookups with Vlookup you need index match. try this. ==INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Be careful of the line wrap it's all one line Mike "Belinda7237" wrote: I have data that needs to be transferred over from one month to the next - 2 seperate workbooks. I wanted to use a vlookup formula to carry over the data: VLOOKUP(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!$A:$F,-6,0) my problem is the field that needs to be populated is to the left of the unique id field and using a negative sign doesnt work. Also moving the columns isn't feasable. Also, if nothing is in the field I want it to leave the field blank instead of putting #N/A can I accomplish this with a different function? |
Vlookup problem
How did thst == creep in it should be
=INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Mike "Mike H" wrote: Belinda, You can't do left lookups with Vlookup you need index match. try this. ==INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Be careful of the line wrap it's all one line Mike "Belinda7237" wrote: I have data that needs to be transferred over from one month to the next - 2 seperate workbooks. I wanted to use a vlookup formula to carry over the data: VLOOKUP(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!$A:$F,-6,0) my problem is the field that needs to be populated is to the left of the unique id field and using a negative sign doesnt work. Also moving the columns isn't feasable. Also, if nothing is in the field I want it to leave the field blank instead of putting #N/A can I accomplish this with a different function? |
Vlookup problem
I missed the bit about hiding NA try this
=IF(ISNA(INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1)),"",INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1)) Lots of line wraps and still one line. Mike "Mike H" wrote: How did thst == creep in it should be =INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Mike "Mike H" wrote: Belinda, You can't do left lookups with Vlookup you need index match. try this. ==INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Be careful of the line wrap it's all one line Mike "Belinda7237" wrote: I have data that needs to be transferred over from one month to the next - 2 seperate workbooks. I wanted to use a vlookup formula to carry over the data: VLOOKUP(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!$A:$F,-6,0) my problem is the field that needs to be populated is to the left of the unique id field and using a negative sign doesnt work. Also moving the columns isn't feasable. Also, if nothing is in the field I want it to leave the field blank instead of putting #N/A can I accomplish this with a different function? |
Vlookup problem
Take a look at this when you have a chance:
http://www.contextures.com/xlFunctions03.html Regards, Ryan--- -- RyGuy "Mike H" wrote: I missed the bit about hiding NA try this =IF(ISNA(INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1)),"",INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1)) Lots of line wraps and still one line. Mike "Mike H" wrote: How did thst == creep in it should be =INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Mike "Mike H" wrote: Belinda, You can't do left lookups with Vlookup you need index match. try this. ==INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Be careful of the line wrap it's all one line Mike "Belinda7237" wrote: I have data that needs to be transferred over from one month to the next - 2 seperate workbooks. I wanted to use a vlookup formula to carry over the data: VLOOKUP(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!$A:$F,-6,0) my problem is the field that needs to be populated is to the left of the unique id field and using a negative sign doesnt work. Also moving the columns isn't feasable. Also, if nothing is in the field I want it to leave the field blank instead of putting #N/A can I accomplish this with a different function? |
Vlookup problem
My invoice doc got retitled - so i used my new doc and inserted the formula
but am getting an error in formula - here is my new formula: IF(ISNA(INDEX('[Bundled Report 06-08.xls]CAU - Maturing Loans'!$A:$A,MATCH,(G14,'[Bundled Report 06-08.xls]CAU - Maturing Loans'!$F:$F,FALSE,1))"",INDEX('[Bundled Report 06-08.xls]CAU - Maturing Loans'!$A:$A,MATCH,(G14,'[Bundled Report 06-08.xls]CAU - Maturing Loans'!$F$F,FALSE),1)) Am i missing something? Thanks again for all your help! "Mike H" wrote: I missed the bit about hiding NA try this =IF(ISNA(INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1)),"",INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1)) Lots of line wraps and still one line. Mike "Mike H" wrote: How did thst == creep in it should be =INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Mike "Mike H" wrote: Belinda, You can't do left lookups with Vlookup you need index match. try this. ==INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Be careful of the line wrap it's all one line Mike "Belinda7237" wrote: I have data that needs to be transferred over from one month to the next - 2 seperate workbooks. I wanted to use a vlookup formula to carry over the data: VLOOKUP(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!$A:$F,-6,0) my problem is the field that needs to be populated is to the left of the unique id field and using a negative sign doesnt work. Also moving the columns isn't feasable. Also, if nothing is in the field I want it to leave the field blank instead of putting #N/A can I accomplish this with a different function? |
Vlookup problem
I presume your sheet name is
CAU - Maturing Loans and those hyphens in there are part of the name and not a hangover from Mike's earlier post where they were put in by the newsreader breaking the formula into lines? You need to ensure that the file Bundled Report 06-08.xls is open at the same time as the file with the formula, as you do not have any path details. You have a ) and two commas missing. Also, you can omit the first INDEX, as it is the MATCH function which will return #NA, so try it like this: =IF(ISNA(MATCH,(G14,'[Bundled Report 06-08.xls]CAU - Maturing Loans'! $F:$F,0)),"",INDEX('[Bundled Report 06-08.xls]CAU - Maturing Loans'!$A: $A,MATCH,(G14,'[Bundled Report 06-08.xls]CAU - Maturing Loans'!$F$F,0), 1)) (I prefer typing 0 instead of FALSE). Hope this helps. Pete On Jul 18, 9:27*pm, Belinda7237 wrote: My invoice doc got retitled - so i used my new doc and inserted the formula but am getting an error in formula - here is my new formula: IF(ISNA(INDEX('[Bundled Report 06-08.xls]CAU - Maturing Loans'!$A:$A,MATCH,(G14,'[Bundled Report 06-08.xls]CAU - Maturing Loans'!$F:$F,FALSE,1))"",INDEX('[Bundled Report 06-08.xls]CAU - Maturing Loans'!$A:$A,MATCH,(G14,'[Bundled Report 06-08.xls]CAU - Maturing Loans'!$F$F,FALSE),1)) Am i missing something? Thanks again for all your help! "Mike H" wrote: I missed the bit about hiding NA try this =IF(ISNA(INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1)),"",INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1)) Lots of line wraps and still one line. Mike "Mike H" wrote: How did thst == creep in it should be =INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Mike "Mike H" wrote: Belinda, You can't do left lookups with Vlookup you need index match. try this. ==INDEX('[Bundled Report 06-08.xls]CAU - Invoices'!A:A,MATCH(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!F:F,FALSE),1) Be careful of the line wrap it's all one line Mike "Belinda7237" wrote: I have data that needs to be transferred over from one month to the next - 2 seperate workbooks. I wanted to use a vlookup formula to carry over the data: VLOOKUP(G48,'[Bundled Report 06-08.xls]CAU - Invoices'!$A:$F,-6,0) my problem is the field that needs to be populated is to the left of the unique id field and using a negative sign doesnt work. *Also moving the columns isn't feasable. *Also, if nothing is in the field I want it to leave the field blank instead of putting #N/A can I accomplish this with a different function?- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 01:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com