# Compare two cells and do lookup

#### Jhmtaylor

##### New Member
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### sunnyland

##### Well-known Member
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
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
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
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
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
5125PostHeight4Footingdia(mm)600
6100Sailareaperpost16Noholddownpoints4
7Sub-tendedAngle30SoiltypeCompactedsoil
83MConcreteslabarea100mm
9Waterproofconic
10
12SideOneSideTwo
13Span(Metres)
14Catenarydepth%#REF!#REF!
16
17
1900.0600.06
2050.0850.06
21100.19.90.1
22120.12100.12
23150.15120.15
24150.2
Sheet1

#### Domenic

##### MrExcel MVP
Try...

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

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

#### Jhmtaylor

##### New Member
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.

Replies
5
Views
1K
Replies
0
Views
900
Replies
6
Views
890
Replies
15
Views
867
Replies
0
Views
377

1,171,579
Messages
5,876,281
Members
433,192
Latest member
butterexcel

### 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.

### Which adblocker are you using?

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

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