Compare two cells and do lookup

[Jhmtaylor]

I have a spreadsheet that is used to calculate wind pressure on buildings under maximum wind load. There are 7 values used in the calculation with several have interdependency on other cells.
Eg
Cell C2 is a number field that contains the size of the surface area
Cell B3 has a data list of 5 numbers containing the basic design wind speed (30,41,50,57,64)
Cell B4 is to perform the following (Look at cell B3 and if it contains any value other than 41 then look at cell c2 and if it contains a value <10, insert value1, if it contains a value >9 and <100 insert a value of 0.9 and if it contains a value >99 insert a value of 0.8

Problem 2 is similar but with a twist

Cell f3 contains data list with 5 items (steel, concrete, glass, ceramics, fabric)

Cell G3 contains data size input

Cell G4 is required to look up cell f3 and if the value is anything other fabric then to perform a look up function in the range H4:i10, if the value in G4 is fabric then to do a look up in the range J4:k10

Suggestions would be sincerely appreciated

 

[sunnyland]

Hello,

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			SurfaceSize
2			100			material	size					List1	List2
3	WindSpeed	50				concrete	steel					30	steel
4		0.8					120	fabric	200	steel	120	41	concrete
5										concrete	130	50	glass
6										glass	140	57	ceramics
7										ceramics	150	64	fabric
8
9
10
Sheet1

I think I did grasp all details.
the formula in b4 is:
=(AND(B3<>41,C2<10)*1)+(AND(B3<>41,C2>99)*0.8)+(AND(B3<>41,C2>=10,C2<=99)*0.9)

the formula in G4 is:
=IF(G3<>"fabric",VLOOKUP(G3,J4:K10,2,FALSE),VLOOKUP(G3,H4:I10,2,FALSE))
In the last formula I added a column to the vlookup array as I didn't see anypoint to return whatever the user typed in G3.

Try it out and see if it returns the expected result.

 

[Jhmtaylor]

I tried

I tried formula one and it partially worked. However if the value in B3 is 41 it is returning a result of zero.
I need to add if the value in B3 is 41 and the value in c2 is greater than 1 than insert a value of 1. I tried fiddling with the formula without success.

 

[sunnyland]

Hello again,

Try the revised formula:

=(AND(B3<>41,C2<10)*1)+(AND(B3<>41,C2>99)*0.8)+(AND(B3<>41,C2>=10,C2<=99)*0.9)+(AND(B3=41,C2>1)*1)

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			SurfaceSize
2			58			material	size					List1	List2
3	WindSpeed	41				concrete	steel					30	steel
4		1					120	fabric	200	steel	120	41	concrete
5										concrete	130	50	glass
6										glass	140	57	ceramics
7										ceramics	150	64	fabric
8
9
10
Sheet1

 

[Jhmtaylor]

Hi,

Problem 1 has been solved. Thank you very much.

I have errors with problem 2 not working. I think I have bamboozled myself with the example. I would like to post an extract of the real spreadsheet but do not know how to use the HTML maker. Can you point me to a link on how to use HTML maker
[/img]

 

[Jhmtaylor]

I have finally worked out how to use HTML. The light version works for me.

The formula is for J14. Required to look at cell D4, if shade to find the the value in that appears in D5 in row1 of F19:g23 and insert the corresponding value in row2. However if any other value to use the range H19:I23.

Aerosail Sail, Column and Footing Specifier 2006.xls
	D	E	F	G	H	I	J	K	L
4	Shade			Date	7/10/2006	Time		17:49:16
5	12		5	PostHeight	4	Footingdia(mm)			600
6	100	Sailareaperpost		16	Noholddownpoints			4
7		Sub-tendedAngle		30	Soiltype	Compactedsoil
8	3M	Concreteslabarea		100mm
9	Waterproofconic
10
11		Option1WindloadCalculation-Pre-tensionmethod
12							SideOne		SideTwo
13		Span(Metres)
14		Catenarydepth%					#REF!		#REF!
15	FrangibleLink	Catenarydepth(Metres)					#REF!		#REF!
16
17
18				Shade		Waterproof
19			0	0.06	0	0.06
20			5	0.08	5	0.06
21			10	0.1	9.9	0.1
22			12	0.12	10	0.12
23			15	0.15	12	0.15
24					15	0.2
Sheet1

 

[Domenic]

Try...

=IF(D4<>"",IF(D5<>"",VLOOKUP(D5,IF(D4="Shade",F19:G23,H19:I24),2),""),"")

Or, if D4 will only contain either 'Shade' or 'Waterproof', maybe...

=IF(D4<>"",IF(D5<>"",VLOOKUP(D5,CHOOSE(MATCH(D4,{"Shade","Waterproof"},0),F19:G23,H19:I24),2),""),"")

Also, what if D5 contains a number greater than 15? What should the formula return?

 

[Jhmtaylor]

It is working well. I have been wrestling with this problem on and off for years until I found this site. It really is a fantastic resource and to know people with far greater experience so generously share their knowledge with newbies is inspirational.