match or vlookup function formula help

[Belinda7237]

I have a spreadsheet that has invoice numbers in column F which i want to
use to link my data into another worksheet.

0001173631

and in another worksheet I have a list of accts but the field that contains
the invoice number is formatted with locationnumber/invoicenumber/amount all
concatenated together like this:

0001700011736310000000331

I wanted to link so that i can return the value in column H of the first
shreadsheet by using the invoice number but wasnt sure how to accomplish - I
have it like this:

=VLOOKUP(F2,'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,1,0)
and it doesnt work as F2 is the long number

 

[Pete_UK]

Try this:

=VLOOKUP(MID(F2,6,10),'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,
1,0)

Hope this helps.

Pete

 

[ryguy7272]

Give this a go:
=SUMPRODUCT(ISNUMBER(SEARCH("*"&A1,A1:A5))*((H1:H5)))


Regards,
Ryan---

--
RyGuy

Try this:

=VLOOKUP(MID(F2,6,10),'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,
1,0)

Hope this helps.

Pete

I have a spreadsheet that has invoice numbers in column F which i want to
use to link my data into another worksheet.

0001173631

and in another worksheet I have a list of accts but the field that contains
the invoice number is formatted with locationnumber/invoicenumber/amount all
concatenated together like this:

0001700011736310000000331

I wanted to link so that i can return the value in column H of the first
shreadsheet by using the invoice number but wasnt sure how to accomplish - I
have it like this:

=VLOOKUP(F2,'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,1,0)
and it doesnt work as F2 is the long number

 

[Belinda7237]

your the best, works perfectly - i knew i should have asked the community two
days ago!

Try this:

=VLOOKUP(MID(F2,6,10),'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,
1,0)

Hope this helps.

Pete

I have a spreadsheet that has invoice numbers in column F which i want to
use to link my data into another worksheet.

0001173631

and in another worksheet I have a list of accts but the field that contains
the invoice number is formatted with locationnumber/invoicenumber/amount all
concatenated together like this:

0001700011736310000000331

I wanted to link so that i can return the value in column H of the first
shreadsheet by using the invoice number but wasnt sure how to accomplish - I
have it like this:

=VLOOKUP(F2,'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,1,0)
and it doesnt work as F2 is the long number

 

[Pete_UK]

Thanks for the feedback, Belinda - you'll know what to do next time !!

Pete

your the best, works perfectly - i knew i should have asked the communitytwo
days ago!

Try this:

=VLOOKUP(MID(F2,6,10),'[East 12 months of Past Dues.xls]Sheet1'!$F:$G,
1,0)

Hope this helps.

Pete