Hi

I am a novice MS Excel 2016 on Windows 10 and am having trouble with a formulae.

H4 is where I want the result to be.

I have 3x tables (TYPE_AE, TYPE_BCFG and TYPE_DH).

I want Cell H3 to check the distance in cell B3 (450-549), then check the type in cell D3 (A, B, C, D etc). then decide which of 3x tables to look at (tables TYPE_AE, TYPE_BCFG or TYPE_DH) then use the value from G3 (109.09) to find it's nearest value in the relevant table, then display in cell H3 what the plumb value is above the G3 value of 109) in the respective table (eg. 8 Plums).

but how do i put this into formulae?
Please help.

Thanks.


B C D E F G H J K
2 DIST TYPE
3 550-A 1.2 1.1 1.09 109.09 #N/A
4
5
6
7
8
9
10 TYPES A & E
11 2 Plums 3 Plums 4 Plums 5 Plums 6 Plums 7 Plums 8 Plums 9 Plums 10 Plums 11 Plums
12 200-249 89 92 94 95 96 97 98 98 99 99 105
13 250-349 81 87 90 92 93 95 96 97 98 100 109
14 350-449 73 81 86 88 90 93 94 96 97 100 109
15 450-549 63 74 80 84 87 90 92 95 96 100 109
16
17
18 TYPES B, C, F & G
19 2 Plums 3 Plums 4 Plums 5 Plums 6 Plums 7 Plums 8 Plums 9 Plums 10 Plums 11 Plums
20 200-249 89 92 94 95 96 97 98 98 99 99
21 250-349 81 87 90 92 93 95 96 97 98 100
22 350-449 73 81 86 88 90 93 94 96 97 100
23 450-549 63 74 80 84 87 90 92 95 96 100
24
25
26 TYPES D & H
27 2 Plums 3 Plums 4 Plums 5 Plums 6 Plums 7 Plums 8 Plums 9 Plums 10 Plums 11 Plums
28 200-249 89 92 94 95 96 97 98 98 99 99
29 250-349 81 87 90 92 93 95 96 97 98 100
30 350-449 73 81 86 88 90 93 94 96 97 100


31 450-549 63 74 80 84 87 90 92 95 96 100

If possible, attach the actual sheet, so we can better see what the values are in the cells. For example, does C12 have "200-249" or is that in two different cells?

Hi Steve,
Thanks for your reply.
Please see below.
Hope this helps.
I am working tomorrow so will get back to you as soon as I can.

Attached Files

EBI200 TEST.docx (36.5 KB, 8 views)

My advice is use a single table instead of 3 tables, like:
MinDistance, MaxDistance, Type, Plums, MinValue, MaxValue
200 250 A 2 89 92
200 250 A 3 92 94
...
450 550 E 11 100 110
...
450 550 H 11 100 110

Now your calculation will be a simple SUMIFS() formula.

Still not sure I understand (I'm just a noob, by the way), but...

You mentioned "check the distance in cell B3 (450-549)." Will that value always be exactly the same (i.e. matching strings) as one of the values in the NORMAL POWER tables?

Here is an example what I thought in my yesterday's post.

Enter data from all your 3 normal power tables into table on NormalPower sheet (i filled it only enough to use your example data) - for all frequencies from A through D.

On sheet EBI200, edit the value returned when data were out of range, i.e. the formula returns 0. Currently the empty string is returned when this is the case.

Attached Files

AnExample.xlsx (14.1 KB, 6 views)

Why post a Word document if you have an Excel problem?
It would be nice we did not have to retype your data, and, no, I did not even open the Word document

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I think he may have been responding to my inquiry about the content of a particular column. The Word doc (which contains a screenshot) did help a little. ...though yes, I was expecting an xls file.

Anyway... Arvi's recommendation is the way to go. If at all possible, convert your tables into a more "Excel-friendly" format.

Thanks Avril

Thanks to both Avril and Steve, but I have been sent a formulae which involves minimal changes and uses VLOOKUP, INDEX and MAPPING. Thanks anyway.