|
Lookup function
This may have already been covered but I cannot find it...
What I need is this, Simple Vlookup, but I need to return a value if it matches a text filter Each lookup value will return several results, but I only want a return if it matches.. So, if I were to write it how I think it should work.. =VLOOKUP(B2,Array,5,True if Match(text)) |
Lookup function
=IF(ISNA(VLOOKUP(B2,Array,5,FALSE)),"",VLOOKUP(B2, Array,5,FALSE))
If this post helps click Yes --------------- Jacob Skaria "Michael" wrote: This may have already been covered but I cannot find it... What I need is this, Simple Vlookup, but I need to return a value if it matches a text filter Each lookup value will return several results, but I only want a return if it matches.. So, if I were to write it how I think it should work.. =VLOOKUP(B2,Array,5,True if Match(text)) |
Lookup function
Do you want to compare the looked up value with a text string and if
it doesn't match give something else? It is not clear what you mean by "each lookup vlaue will return several results". Unless you are using an array function, you are going to get a single result. You can compare that with something and use the IF function to handle the logic. It seems like you just may have this inside out; maybe something more like =IF(VLOOKUP(B2, array, 5, false)="text","text","no match")) Maybe a more detailed example will help. Ken On Jun 2, 12:38*pm, Michael wrote: This may have already been covered but I cannot find it... What I need is this, Simple Vlookup, but I need to return a value if it matches a text filter Each lookup value will return several results, but I only want a return if it matches.. So, if I were to write it how I think it should work.. =VLOOKUP(B2,Array,5,True if Match(text)) |
Lookup function
This is my equation, and am only returning the first reponse, I need to
return the reponse that is the closest match.. So if I have A B ABECY Switch ABECY Router I need router, but the list of possible reponses is not constant. =IF(ISNA(VLOOKUP(B2,'[HSIA Equipment Inventory With GP.xls]A-L'!$A$1:$K$38339,5,FALSE)),"",VLOOKUP(B2,'[HSIA Equipment Inventory With GP.xls]A-L'!$A$1:$K$38339,5,FALSE)) Michael "Jacob Skaria" wrote: =IF(ISNA(VLOOKUP(B2,Array,5,FALSE)),"",VLOOKUP(B2, Array,5,FALSE)) If this post helps click Yes --------------- Jacob Skaria "Michael" wrote: This may have already been covered but I cannot find it... What I need is this, Simple Vlookup, but I need to return a value if it matches a text filter Each lookup value will return several results, but I only want a return if it matches.. So, if I were to write it how I think it should work.. =VLOOKUP(B2,Array,5,True if Match(text)) |
Lookup function
So you are looking up ABECY in this range, and you want Router not
Switch? What is the rule that detemines Router and not Switch? On Jun 2, 1:11*pm, Michael wrote: *This is my equation, and am only returning the first reponse, I need to return the reponse that is the closest match.. So if I have A * * * * * * * * * * * * * * * *B ABECY * * * * * * * * * Switch ABECY * * * * * * * * * Router I need router, but the list of possible reponses is not constant. =IF(ISNA(VLOOKUP(B2,'[HSIA Equipment Inventory With GP.xls]A-L'!$A$1:$K$38339,5,FALSE)),"",VLOOKUP(B2,'[HSIA Equipment Inventory With GP.xls]A-L'!$A$1:$K$38339,5,FALSE)) Michael "Jacob Skaria" wrote: =IF(ISNA(VLOOKUP(B2,Array,5,FALSE)),"",VLOOKUP(B2, Array,5,FALSE)) If this post helps click Yes --------------- Jacob Skaria "Michael" wrote: This may have already been covered but I cannot find it... What I need is this, Simple Vlookup, but I need to return a value if it matches a text filter Each lookup value will return several results, but I only want a return if it matches.. So, if I were to write it how I think it should work.. =VLOOKUP(B2,Array,5,True if Match(text))- Hide quoted text - - Show quoted text - |
Lookup function
To clarify,
I have a sheet in which for value ABECY I need to know what type of router exists onsite In my array I have ABECY which has a number of existing part numbers, but only one has the word router in it. So what I need to do, is Lookup ABECY, check Column 5 for a value if that value contains the text string "Router" then return the contents of the cell, In the below I would need B6 as the response to my look up. (in my equation, column B is actually E) 1 ABECY 1 Port T1-DSU/CSU WAN IC (WIC-1DSU-T1-V2) 2 ABECY 1032 X 1/2 PPH (SC1109) 3 ABECY 24 Port Unmanaged Switch (EZNET-24SW) 4 ABECY 25' Patch Cable Blue 5 ABECY 28 PORT 10/100 ETH SWITCH + 4 Gig ports (SMC6128L2) 6 ABECY 2811 Router - (CISCO2811) 7 ABECY 2950 24 Port Cisco Switch (WS-C2950-24) " wrote: Do you want to compare the looked up value with a text string and if it doesn't match give something else? It is not clear what you mean by "each lookup vlaue will return several results". Unless you are using an array function, you are going to get a single result. You can compare that with something and use the IF function to handle the logic. It seems like you just may have this inside out; maybe something more like =IF(VLOOKUP(B2, array, 5, false)="text","text","no match")) Maybe a more detailed example will help. Ken On Jun 2, 12:38 pm, Michael wrote: This may have already been covered but I cannot find it... What I need is this, Simple Vlookup, but I need to return a value if it matches a text filter Each lookup value will return several results, but I only want a return if it matches.. So, if I were to write it how I think it should work.. =VLOOKUP(B2,Array,5,True if Match(text)) |
Lookup function
Michael
Try this.. with the data in Col A and Col E =INDEX(E1:E100,(A1:A100="ABECY")*(MATCH("*Router*" ,E1:E100,0))) With Abecy and Router in cells F1 and G1 =INDEX(E1:E100,(A1:A100=F1)*(MATCH("*"& G1 &"*",E1:E100,0))) If this post helps click Yes --------------- Jacob Skaria "Michael" wrote: To clarify, I have a sheet in which for value ABECY I need to know what type of router exists onsite In my array I have ABECY which has a number of existing part numbers, but only one has the word router in it. So what I need to do, is Lookup ABECY, check Column 5 for a value if that value contains the text string "Router" then return the contents of the cell, In the below I would need B6 as the response to my look up. (in my equation, column B is actually E) 1 ABECY 1 Port T1-DSU/CSU WAN IC (WIC-1DSU-T1-V2) 2 ABECY 1032 X 1/2 PPH (SC1109) 3 ABECY 24 Port Unmanaged Switch (EZNET-24SW) 4 ABECY 25' Patch Cable Blue 5 ABECY 28 PORT 10/100 ETH SWITCH + 4 Gig ports (SMC6128L2) 6 ABECY 2811 Router - (CISCO2811) 7 ABECY 2950 24 Port Cisco Switch (WS-C2950-24) " wrote: Do you want to compare the looked up value with a text string and if it doesn't match give something else? It is not clear what you mean by "each lookup vlaue will return several results". Unless you are using an array function, you are going to get a single result. You can compare that with something and use the IF function to handle the logic. It seems like you just may have this inside out; maybe something more like =IF(VLOOKUP(B2, array, 5, false)="text","text","no match")) Maybe a more detailed example will help. Ken On Jun 2, 12:38 pm, Michael wrote: This may have already been covered but I cannot find it... What I need is this, Simple Vlookup, but I need to return a value if it matches a text filter Each lookup value will return several results, but I only want a return if it matches.. So, if I were to write it how I think it should work.. =VLOOKUP(B2,Array,5,True if Match(text)) |
Lookup function
Michael, forgot to mention that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Michael Try this.. with the data in Col A and Col E =INDEX(E1:E100,(A1:A100="ABECY")*(MATCH("*Router*" ,E1:E100,0))) With Abecy and Router in cells F1 and G1 =INDEX(E1:E100,(A1:A100=F1)*(MATCH("*"& G1 &"*",E1:E100,0))) If this post helps click Yes --------------- Jacob Skaria "Michael" wrote: To clarify, I have a sheet in which for value ABECY I need to know what type of router exists onsite In my array I have ABECY which has a number of existing part numbers, but only one has the word router in it. So what I need to do, is Lookup ABECY, check Column 5 for a value if that value contains the text string "Router" then return the contents of the cell, In the below I would need B6 as the response to my look up. (in my equation, column B is actually E) 1 ABECY 1 Port T1-DSU/CSU WAN IC (WIC-1DSU-T1-V2) 2 ABECY 1032 X 1/2 PPH (SC1109) 3 ABECY 24 Port Unmanaged Switch (EZNET-24SW) 4 ABECY 25' Patch Cable Blue 5 ABECY 28 PORT 10/100 ETH SWITCH + 4 Gig ports (SMC6128L2) 6 ABECY 2811 Router - (CISCO2811) 7 ABECY 2950 24 Port Cisco Switch (WS-C2950-24) " wrote: Do you want to compare the looked up value with a text string and if it doesn't match give something else? It is not clear what you mean by "each lookup vlaue will return several results". Unless you are using an array function, you are going to get a single result. You can compare that with something and use the IF function to handle the logic. It seems like you just may have this inside out; maybe something more like =IF(VLOOKUP(B2, array, 5, false)="text","text","no match")) Maybe a more detailed example will help. Ken On Jun 2, 12:38 pm, Michael wrote: This may have already been covered but I cannot find it... What I need is this, Simple Vlookup, but I need to return a value if it matches a text filter Each lookup value will return several results, but I only want a return if it matches.. So, if I were to write it how I think it should work.. =VLOOKUP(B2,Array,5,True if Match(text)) |
| All times are GMT +1. The time now is 05:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com