#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 791
Default 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))
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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))

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 109
Default 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))


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 791
Default 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))

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 109
Default 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 -




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 791
Default 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))



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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))



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default 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))


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Data Validation/Lookup function does function correcty Kirkey Excel Worksheet Functions 2 May 25th 09 09:22 PM
Lookup Function noodlehounds Excel Discussion (Misc queries) 4 November 20th 07 01:26 AM
how to combine an IF Function with a lookup function to determine [email protected] Excel Worksheet Functions 1 December 5th 06 07:09 AM
Lookup function Srilesh R Excel Worksheet Functions 0 March 13th 06 03:41 PM
Pivot table doing a lookup without using the lookup function? NGASGELI Excel Discussion (Misc queries) 0 August 2nd 05 05:08 AM


All times are GMT +1. The time now is 12:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"