If , match , vookup . I think .

[Sunline]

Hello all , have looked hard but cannot find answer on here .
In col A there are words , Fast , Good , Dead , Soft , Heavy .
In col B there are the same words as above .
I would like too firstly look in col A , example , if Fast is there then look in col B and if fast , good or dead is there then vlookup sheet2 range CC2:DD999 .
I have conanceated these and given a points scale on the vlookup sheet .
I use vlookup because i am able too change these at will without having to change formular over 170,000 rows .
Hope this makes sence , thanks .

 

[jeffreybrown]

Will this work for you...

=IF(AND(A1="Fast",OR(B1={"Fast","Good","Dead"})),VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),"")

 

[Sunline]

thanks Jeff , will try this out , forgot to mention that col A is always populated , some cells in col B may be blank .
So it wont find anything in Vlookup , hoping i can get it too see a blank cell and then return 0 zero .
This is my original Vlookup function . returns zero if cell blank .
=IF(DE2="",0,VLOOKUP(DE2,Sheet2!$A$1:$B$18,2,0)) ,ignore these cols and ranges .

Your suggestion ,

=IF(AND(A1="Fast",OR(B1={"Fast","Good","Dead"})),VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),"") .

I honestly dont know how to join this too yours , give me a week tho , thanks .

 

[jeffreybrown]

Try...

=IF(DE2="",0,IF(AND(OR(B2={"Fast","Good","Dead"}),A2="Fast"),VLOOKUP(DE2,Sheet2!$A$1:$B$18,2,0),""))

Are you saying you need the formula to validate A and B also?

 

[Sunline]

Ah no sorry im confusing things now , i wasnt sure how to attach the vlookup part onto the end of your first suggestion but also allowing for blank cells in col B .
The DE2 function was an original supplied too me by Aladin . Just showing you an example of function that works and allows blanks and returns zero .
But i will give this a slight amend and try , thanks .

 

[jeffreybrown]

You're welcome and good luck with the rest...post back if you need more help

 

[Sunline]

Hello Jeff , i tried to implement this function but am now getting #VALUE! error .
=IF(DE2="",0,IF(AND(OR(BD2:BF2={"Fast","Good","Dead"}),J2="Fast","Good","Dead"),VLOOKUP(DE2,Sheet2!$A$2:$B$43,2,0),""))

Im thinking im asking things to happen in wrong order .
Col DE2 is looking for answer from vlookup sheet2 which works normally as standard Vlookup .
Maybe first command should be , confirming in col J if "Fast","Good","Dead" are there , then look in cols range BD2:BF2 for "Fast","Good","Dead" and if both true then look in col DE2 and apply Vlookup from sheet2 giving result . If any cells are blank then return zero 0 .
Thanks .

 

[jeffreybrown]

Hello Jeff, i tried to implement this function but am now getting #VALUE! error.
=IF(DE2="",0,IF(AND(OR(BD2:BF2={"Fast","Good","Dead"}),J2="Fast","Good","Dead"),VLOOKUP(DE2,Sheet2!$A$2:$B$43,2,0),""))

The reason it did not work is because you have altered from the above posted formula and most likely because we have not reached a clear understanding of what is where and what is being looked searched for.

Ranges have changed since the first post...

I would like too firstly look in col A , example , if Fast is there then look in col B and if fast , good or dead is there then vlookup sheet2 range CC2:DD999

Im thinking im asking things to happen in wrong order

The order should not be a problem to resolve, but more clarification please

Maybe first command should be, confirming in col J if "Fast", "Good", "Dead" are there

Does this mean anyone of those three words could be in J are all three at one time or any one of the three?

then look in cols range BD2:BF2for "Fast", "Good", "Dead"

In these three cells, what are you looking for? "Fast", "Good", "Dead" all in one cell or somewhere among the three? In any particular order?????

Look at my signature block and by using the HTML maker can you post a simplified version of what you have and what you desire?

 

[Sunline]

Yes well said , col J only has these 5 words , fast , good , dead , soft , heavy , each would always be in own individual cell . Same for col range BD2:BF2 , they may be in any order .
I will deal with soft and heavy once i am able to get function working correctly .
Ok will give the HTML maker another go ,
Thanks .

 

[Sunline]

Hi , having no joy getting HTML maker to work ,
will battle on , thanks .