Compare two cells and do lookup

Jhmtaylor

New Member
Joined
Oct 1, 2006
Messages
10
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello,
Book2
ABCDEFGHIJKLM
1SurfaceSize
2100materialsizeList1List2
3WindSpeed50concretesteel30steel
40.8120fabric200steel12041concrete
5concrete13050glass
6glass14057ceramics
7ceramics15064fabric
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

New Member
Joined
Oct 1, 2006
Messages
10
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

Well-known Member
Joined
Jan 27, 2006
Messages
912
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
ABCDEFGHIJKLM
1SurfaceSize
258materialsizeList1List2
3WindSpeed41concretesteel30steel
41120fabric200steel12041concrete
5concrete13050glass
6glass14057ceramics
7ceramics15064fabric
8
9
10
Sheet1
 

Jhmtaylor

New Member
Joined
Oct 1, 2006
Messages
10

ADVERTISEMENT

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

New Member
Joined
Oct 1, 2006
Messages
10
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
DEFGHIJKL
4ShadeDate7/10/2006Time17:49:16
5125PostHeight4Footingdia(mm)600
6100Sailareaperpost16Noholddownpoints4
7Sub-tendedAngle30SoiltypeCompactedsoil
83MConcreteslabarea100mm
9Waterproofconic
10
11Option1WindloadCalculation-Pre-tensionmethod
12SideOneSideTwo
13Span(Metres)
14Catenarydepth%#REF!#REF!
15FrangibleLinkCatenarydepth(Metres)#REF!#REF!
16
17
18ShadeWaterproof
1900.0600.06
2050.0850.06
21100.19.90.1
22120.12100.12
23150.15120.15
24150.2
Sheet1
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,705
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

New Member
Joined
Oct 1, 2006
Messages
10
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.
 

Forum statistics

Threads
1,137,332
Messages
5,680,875
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top