| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
| Is there a formula for searching for any one of a list of strings? | Excel Worksheet Functions | |||
| Formula for searching for a text string | Excel Discussion (Misc queries) | |||
| Searching a spreadsheet | Excel Discussion (Misc queries) | |||
| extracting data from a spreadsheet by searching on columns | New Users to Excel | |||
Linear Mode
