If then with lookup?

[bellashahar]

I think I am trying to do an IF THEN statement, but have a range of text to return if requirements are met. Could someone advise how this can be done?

Here is my example:

tab "Master Inventory" E2 is where I want to look and if it matchs my list in my lookup, I want it to return the text from tab "Lookup" (list to lookup is in A1-A22)

Hope this make sense. Any help would be appreciated.

thank you,

 

[sassy]

you should use a vlookup (vertical data) or hlookup (horizontal data)

=vlookup(E2,'lookup'!A1:A22,1,False)

The '1' is the cell in the row that you want to extract the data from. Modify it based on if you want it to pull another cell in that row.

 

[Aladin Akyurek]

I think I am trying to do an IF THEN statement, but have a range of text to return if requirements are met. Could someone advise how this can be done?

Here is my example:

tab "Master Inventory" E2 is where I want to look and if it matchs my list in my lookup, I want it to return the text from tab "Lookup" (list to lookup is in A1-A22)

Hope this make sense. Any help would be appreciated.

thank you,

Hard to follow...

Does E2 on Master Inventory house a value that you want to match against (compare with) A1:A22 on Lookup? If so, what is the result range on Lookup?

 

[bellashahar]

Yes, E2 has text that matches a portion of the list on my lookup.

For example, in E2 the cells have "12.2(31); in the lookup list I am looking for anything that has this 12.2(31) value. My list that I want to look at may look like this "Catalyst 4500 series 12.2(31)SG", so I want F2 to return the entire contents of my lookup if it has that portion on the data in it.

Does that make more sense?

thanks again for helping.

 

[Haseeb Avarakkan]

Hello, Try any of these

=LOOKUP(1E+100,SEARCH(E2,$A$1:$A$22),$A$1:$A$22)

=VLOOKUP("*"&E2&"*",$A$1:$A$22,1,FALSE)

=INDEX($A$1:$A$22,MATCH("*"&E2&"*",$A$1:$A$22,0))

 

[bellashahar]

Hello, Try any of these

=LOOKUP(1E+100,SEARCH(E2,$A$1:$A$22),$A$1:$A$22)

=VLOOKUP("*"&E2&"*",$A$1:$A$22,1,FALSE)

=INDEX($A$1:$A$22,MATCH("*"&E2&"*",$A$1:$A$22,0))

thank you so much! However $A$1:$A$22 is on a tab called "lookup". How do I get that to work?

 

[Aladin Akyurek]

Yes, E2 has text that matches a portion of the list on my lookup.

For example, in E2 the cells have "12.2(31); in the lookup list I am looking for anything that has this 12.2(31) value. My list that I want to look at may look like this "Catalyst 4500 series 12.2(31)SG", so I want F2 to return the entire contents of my lookup if it has that portion on the data in it.

Does that make more sense?

thanks again for helping.

Try in F2...

Either:

=INDEX(Lookup!$A$1:$A$22,MATCH("*"&E2&"*",Lookup!$A$1:$A$22,0))

Or:

=VLOOKUP("*"&E2&"*",Lookup!$A$1:$A$22,1,0)

To prevent #N/A for showing up...

If you are on Excel 2007 or later...

=IFERROR(INDEX(Lookup!$A$1:$A$22,
    MATCH("*"&E2&"*",Lookup!$A$1:$A$22,0)),"Not Found")

On all systems...

=IF(ISNUMBER(MATCH("*"&E2&"*",Lookup!$A$1:$A$22,0)),
   INDEX(Lookup!$A$1:$A$22,MATCH("*"&E2&"*",Lookup!$A$1:$A$22,0))
   "Not Found")

 

[bellashahar]

it worked!!! THANK YOU SO MUCH! what a great help!