Lookup function

[Michael]

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))

 

[Jacob Skaria]

=IF(ISNA(VLOOKUP(B2,Array,5,FALSE)),"",VLOOKUP(B2,Array,5,FALSE))

If this post helps click Yes

 

[krcowen]

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

 

[Michael]

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

 

[krcowen]

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?

 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

=IF(ISNA(VLOOKUP(B2,Array,5,FALSE)),"",VLOOKUP(B2,Array,5,FALSE))

If this post helps click Yes

"Michael" wrote:

- Show quoted text -

 

[Michael]

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)

 

[Jacob Skaria]

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