Formula for searching spreadsheet

[Lozza65]

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?

 

[Pete_UK]

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$2742,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

 

[Lozza65]

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

 

[Domenic]

Try...

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

Hope this helps!

 

[Pete_UK]

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

 

[Domenic]

... 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!

 

[Lozza65]

Thanks heaps for you help. I now have it working

 

[Pete_UK]

You're welcome - glad to be of help.

Pete

Thanks heaps for you help. I now have it working






- Show quoted text -