Formula for searching spreadsheet
 

Hi,
I am using the following formula to search a sheet for data and then return
it to a summary sheet in the same workbook.
=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"
",VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE))

$A5 is the reference in the summary sheet and is a code of a product e.g.
AP282003. My problem is that in the sheet I am searching the products can
have a letter at the end e.g. AP282003M or AP282003MH or just be AP282003.
How can I modify the code to pick up the all 3 items?

Formula for searching spreadsheet
 

VLOOKUP (and MATCH) will only return the first matched value from a
table if there are duplicates, so you have to go about this slightly
differently. One way would be to extend your formula like this:

=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"",VLOOKUP($A5,FGU!
$A$2:$H$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"M", FGU!$A$2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"M",FGU!$A$2:$H
$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"MH",FGU!$A $2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"MH",FGU!$A$2:$H$27 42,4,FALSE))

So, with AP282003 in A5, the formula would look first for a match with
that one, then would look for a match on AP282003M and finally
AP282003MH. I suspect, though, that this is not exactly what you mean.

Hope this helps.

Pete

On Nov 26, 11:47 pm, Lozza65
wrote:
Hi,
I am using the following formula to search a sheet for data and then return
it to a summary sheet in the same workbook.
=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"
",VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE))

$A5 is the reference in the summary sheet and is a code of a product e.g.
AP282003. My problem is that in the sheet I am searching the products can
have a letter at the end e.g. AP282003M or AP282003MH or just be AP282003.
How can I modify the code to pick up the all 3 items?

Formula for searching spreadsheet
 

Thanks Pete, it has helped. The formula works now I need to get the values
to sum. At the moment it appears as two separate numbers in the same field.
e.g. 12 10 instead of 22. Do you have any suggestions?
Regards
Lozza

"Pete_UK" wrote:

VLOOKUP (and MATCH) will only return the first matched value from a
table if there are duplicates, so you have to go about this slightly
differently. One way would be to extend your formula like this:

=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"",VLOOKUP($A5,FGU!
$A$2:$H$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"M", FGU!$A$2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"M",FGU!$A$2:$H
$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"MH",FGU!$A $2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"MH",FGU!$A$2:$H$27 42,4,FALSE))

So, with AP282003 in A5, the formula would look first for a match with
that one, then would look for a match on AP282003M and finally
AP282003MH. I suspect, though, that this is not exactly what you mean.

Hope this helps.

Pete

On Nov 26, 11:47 pm, Lozza65
wrote:
Hi,
I am using the following formula to search a sheet for data and then return
it to a summary sheet in the same workbook.
=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"
",VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE))

$A5 is the reference in the summary sheet and is a code of a product e.g.
AP282003. My problem is that in the sheet I am searching the products can
have a letter at the end e.g. AP282003M or AP282003MH or just be AP282003.
How can I modify the code to pick up the all 3 items?

Formula for searching spreadsheet
 

Try...

=IF(ISNA(VLOOKUP($A5&"*",FGU!$A$2:$H$2742,4,0)),"" ,VLOOKUP($A5&"*",FGU!$A
$2:$H$2742,4,0))

Hope this helps!

In article ,
Lozza65 wrote:

Hi,
I am using the following formula to search a sheet for data and then return
it to a summary sheet in the same workbook.
=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"
",VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE))

$A5 is the reference in the summary sheet and is a code of a product e.g.
AP282003. My problem is that in the sheet I am searching the products can
have a letter at the end e.g. AP282003M or AP282003MH or just be AP282003.
How can I modify the code to pick up the all 3 items?

Formula for searching spreadsheet
 

I have basically taken your formula and repeated it three times with
slight changes and joined each together by means of & - you could
change this symbol to + twice in the formula, but you will also have
to change the "" (or you have it as " ") to a zero to be returned if
there is no match.

Hope this helps.

Pete

On Nov 27, 1:52 am, Lozza65 wrote:
Thanks Pete, it has helped. The formula works now I need to get the values
to sum. At the moment it appears as two separate numbers in the same field.
e.g. 12 10 instead of 22. Do you have any suggestions?
Regards
Lozza



"Pete_UK" wrote:
VLOOKUP (and MATCH) will only return the first matched value from a
table if there are duplicates, so you have to go about this slightly
differently. One way would be to extend your formula like this:

=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"",VLOOKUP($A5,FGU!
$A$2:$H$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"M", FGU!$A$2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"M",FGU!$A$2:$H
$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"MH",FGU!$A $2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"MH",FGU!$A$2:$H$27 42,4,FALSE))

So, with AP282003 in A5, the formula would look first for a match with
that one, then would look for a match on AP282003M and finally
AP282003MH. I suspect, though, that this is not exactly what you mean.

Hope this helps.

Pete

On Nov 26, 11:47 pm, Lozza65
wrote:
Hi,
I am using the following formula to search a sheet for data and then return
it to a summary sheet in the same workbook.
=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"
",VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE))

$A5 is the reference in the summary sheet and is a code of a product e.g.
AP282003. My problem is that in the sheet I am searching the products can
have a letter at the end e.g. AP282003M or AP282003MH or just be AP282003.
How can I modify the code to pick up the all 3 items?- Hide quoted text -

- Show quoted text -

Formula for searching spreadsheet
 

In article ,
Lozza65 wrote:

... I need to get the values to sum...

Is this what you mean?

=SUMIF(FGU!$A$2:$A$2742,$A5&"*",FGU!$D$2:$D$2742)

Hope this helps!

Formula for searching spreadsheet
 

Thanks heaps for you help. I now have it working

"Pete_UK" wrote:

I have basically taken your formula and repeated it three times with
slight changes and joined each together by means of & - you could
change this symbol to + twice in the formula, but you will also have
to change the "" (or you have it as " ") to a zero to be returned if
there is no match.

Hope this helps.

Pete

On Nov 27, 1:52 am, Lozza65 wrote:
Thanks Pete, it has helped. The formula works now I need to get the values
to sum. At the moment it appears as two separate numbers in the same field.
e.g. 12 10 instead of 22. Do you have any suggestions?
Regards
Lozza



"Pete_UK" wrote:
VLOOKUP (and MATCH) will only return the first matched value from a
table if there are duplicates, so you have to go about this slightly
differently. One way would be to extend your formula like this:

=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"",VLOOKUP($A5,FGU!
$A$2:$H$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"M", FGU!$A$2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"M",FGU!$A$2:$H
$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"MH",FGU!$A $2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"MH",FGU!$A$2:$H$27 42,4,FALSE))

So, with AP282003 in A5, the formula would look first for a match with
that one, then would look for a match on AP282003M and finally
AP282003MH. I suspect, though, that this is not exactly what you mean.

Hope this helps.

Pete

On Nov 26, 11:47 pm, Lozza65
wrote:
Hi,
I am using the following formula to search a sheet for data and then return
it to a summary sheet in the same workbook.
=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"
",VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE))

$A5 is the reference in the summary sheet and is a code of a product e.g.
AP282003. My problem is that in the sheet I am searching the products can
have a letter at the end e.g. AP282003M or AP282003MH or just be AP282003.
How can I modify the code to pick up the all 3 items?- Hide quoted text -

- Show quoted text -

Formula for searching spreadsheet
 

You're welcome - glad to be of help.

Pete

On Nov 28, 2:03 am, Lozza65 wrote:
Thanks heaps for you help. I now have it working



"Pete_UK" wrote:
I have basically taken your formula and repeated it three times with
slight changes and joined each together by means of & - you could
change this symbol to + twice in the formula, but you will also have
to change the "" (or you have it as " ") to a zero to be returned if
there is no match.

Hope this helps.

Pete

On Nov 27, 1:52 am, Lozza65 wrote:
Thanks Pete, it has helped. The formula works now I need to get the values
to sum. At the moment it appears as two separate numbers in the same field.
e.g. 12 10 instead of 22. Do you have any suggestions?
Regards
Lozza

"Pete_UK" wrote:
VLOOKUP (and MATCH) will only return the first matched value from a
table if there are duplicates, so you have to go about this slightly
differently. One way would be to extend your formula like this:

=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"",VLOOKUP($A5,FGU!
$A$2:$H$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"M", FGU!$A$2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"M",FGU!$A$2:$H
$2742,4,FALSE))&IF(ISERROR(VLOOKUP($A5&"MH",FGU!$A $2:$H
$2742,4,FALSE)),"",VLOOKUP($A5&"MH",FGU!$A$2:$H$27 42,4,FALSE))

So, with AP282003 in A5, the formula would look first for a match with
that one, then would look for a match on AP282003M and finally
AP282003MH. I suspect, though, that this is not exactly what you mean.

Hope this helps.

Pete

On Nov 26, 11:47 pm, Lozza65
wrote:
Hi,
I am using the following formula to search a sheet for data and then return
it to a summary sheet in the same workbook.
=IF(ISERROR(VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE)) ,"
",VLOOKUP($A5,FGU!$A$2:$H$2742,4,FALSE))

$A5 is the reference in the summary sheet and is a code of a product e.g.
AP282003. My problem is that in the sheet I am searching the products can
have a letter at the end e.g. AP282003M or AP282003MH or just be AP282003.
How can I modify the code to pick up the all 3 items?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -