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]

You could also look at

http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or upload your workbook to a site like Box.net and post the link here.

 

[Michael M]

Hi Kevin
What part of the Mr Excel HTML Maker are you having trouble with ?
Once you've downloaded it.
Highlight a sample of your data.
Select the MrExcel HTML Menu
Select the 2nd Item from the dropdown
Now come back to this forum and select paste.

It looks like computer code, but once you submit it looks like Excel again.

 

[Sunline]

Hi guys , ok i finally mastered a hmtl maker , thanks for your feedback on that .
So that brings me back too trying too ask this correctly now .
Cols DB and IJ have data which relates to each other , so what happens in DB2 corresponds with IJ2 , same for cols DC to IK up too DF too col IN .
Would like function too ,
Look too see if cell J2 contains Fast , Good , Dead , if it does then look at cell IJ2 for the same , if it does then look in DB2 and apply vlookup .
If these words werent in col IJ2 or cells were blank then always return zero 0 .
Col J is always populated but as per this example there can be blank cells in cols DB to IN . .
Using this example looking at col J the first 5 say heavy , there were 125 of these down col J so referencing using this small block wont work if that makes sence . I have cut this to fit on here only .
Thanks .

Excel Workbook
	J	DB	DC	DD	DE	DF	IJ	IK	IL	IM	IN
1	Con	PL5	PL4	PL3	PL2	PL1	Con5	Con4	Con3	Con2	Con1
2	Heavy	8	4	6	3	10=	Dead	Dead	Slow	Good	Good
3	Heavy	*	*	*	*	9	*	*	*	*	Heavy
4	Heavy	*	*	*	*	*	*	*	*	*	*
5	Heavy	6	4	9	5	1	Heavy	Good	Heavy	Good	Slow
6	Heavy	*	*	*	7	9	*	*	*	Good	Good
7	Fast	6	11	7	11	8	Dead	Heavy	Heavy	Heavy	Good
8	Fast	6	4	7	4	3	Good	Slow	Slow	Dead	Good
9	Fast	8	4	6	3	1=	Dead	Dead	Slow	Good	Good
10	Fast	*	*	*	*	9	*	*	*	*	Heavy
11	Good	*	*	12	5	4	*	*	Slow	Dead	Good
12	Good	*	*	*	*	*	*	*	*	*	*
13	Good	*	*	*	7	9	*	*	*	Good	Good
14	Good	5	11	7	11	8	Dead	Heavy	Heavy	Heavy	Good
15	Dead	8	4	6	3	11=	Dead	Dead	Slow	Good	Good
16	Dead	*	*	*	*	9	*	*	*	*	Heavy
17	Dead	*	*	12	5	4	*	*	Slow	Dead	Good
18	Slow	*	*	*	*	*	*	*	*	*	*
19	Slow	6	4	9	5	1	Heavy	Good	Heavy	Good	Slow
Sheet1

 

[jeffreybrown]

Would like function too, Look too see if cell J2 contains Fast, Good, Dead, if it does then look at cell IJ2 for the same, if it does then look in DB2 and apply vlookup.

If these words weren't in col IJ2 or cells were blank then always return zero.

The formula below will do what you ask above...

=IF(AND(OR(J2={"Fast","Good","Dead"}),OR(IJ2={"Fast","Good","Dead"})),VLOOKUP(DB2,$A$2:$C$100,2,0),0)

Cols DB and IJ have data which relates to each other, so what happens in DB2 corresponds with IJ2, same for cols DC to IK up too DF too col IN.

Not all that sure what you want done with the quote above. You now seem to be saying you want to check DC2 against IK2, DD2 against IL2, etc.

If so, do you now want a vlookup against DC2, DD2, etc? Where do you want all these results? Is this all in one cell or in five different cells?

I'm sorry but I just need a little more insight into what you are trying to accomplish.

 

[Sunline]

Hi Jeff , no im not wanting to do anything as per my quote .
I tried your function and am getting either 0 or #N/A in all cells .

Not sure why myself , have checked thoroughly for correct cols and ranges etc .
=IF(AND(OR(J2={"Fast","Good","Dead"}),OR(IV2={"Fast","Good","Dead"})),VLOOKUP(DF2,Sheet2!$A$2:$B$43,2,0),0)

The Vlookup is on sheet 2 which i tried putting in adding too your function you have last supplied .
Thanks .

 

[jeffreybrown]

I would be happy to take a look at your workbook. If so, PM me your e-mail or load in to a download site like Box.net.

 

[Sunline]

Ok got this to work .
=IF(AND(OR(J2={"Fast"}),OR(JH2={"Fast","Good"})),VLOOKUP(DF2,Sheet2!$A$2:$B$43,2,0),0)

I know the cols have changed from my post , "Hi guys , ok i finally mastered a hmtl maker"

Adjust your cols to suit if viewing .

Thanks both Jeff and Michael for your help with this function .

 

[jeffreybrown]

Hi Kevin,

In EO2 try...

=IF(AND(J2="Fast",OR(JH2={"Fast","Good"}),AND(BY2>=9999.99,BY2<20000),AND(DZ2>=0.5,DZ2<2.6),AL2="PQ"),VLOOKUP(DF2,Sheet2!$A$2:$B$43,2,0),0)

 

[Sunline]

Hello Jeff , how would i take this part out of this formular AL2="PQ" and still get it too do the rest , just giving myself another option .

=IF(AND(J2="Fast",OR(JH2={"Fast","Good"}),AND(BY2>=9999.99,BY2<20000),AND(DZ2>=0.5,DZ2<2.6),AL2="PQ"),VLOOKUP(DF2,Sheet2!$A$2:$B$43,2,0),0)

Thanks .

 

[jeffreybrown]

Hi Kevin,

Remove this...AL2="PQ"

=IF(AND(J2="Fast",OR(JH2={"Fast","Good"}),AND(BY2>=9999.99,BY2<20000),AND(DZ2>=0.5,DZ2<2.6)),VLOOKUP(DF2,Sheet2!$A$2:$B$43,2,0),0)

The key...check your opening brackets against the closing brackets...Where there is a opening there must be a closing

and

Watch the color of the brackets which should help you see your pairs of brackets