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?

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?

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?

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?

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?

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?

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 -