Please Help!

[pkb68]

Please help! I am working in table 1 to fill-in “Index A” with data from “Index B” of my “Lookup Table” by finding which range (Max:Min) covers the “Value”.
As an example, B2=Lookup Table: C2=31 as 17950 is within A2 and B2 of lookup table.
Table 1:
<table class="MsoNormalTable" style="width: 144.85pt; margin-left: 4.55pt; border-collapse: collapse;" width="193" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 19.75pt;"> <td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 32.35pt; height: 19.75pt;" valign="top" width="43">

<o> </o>​

</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt; height: 19.75pt;" width="78">

A<o></o>​

</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; width: 0.75in; height: 19.75pt;" width="72">

B<o></o>​

</td> </tr> <tr style="height: 19.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 32.35pt; height: 19.75pt;" valign="top" width="43">

1<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt; height: 19.75pt;" width="78">

Value<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 0.75in; height: 19.75pt;" width="72">

Index A<o></o>​

</td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 32.35pt; height: 15.75pt;" valign="top" width="43">

2<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt; height: 15.75pt;" valign="bottom" width="78" nowrap="nowrap">

17950<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 0.75in; height: 15.75pt;" valign="bottom" width="72" nowrap="nowrap">

31<o></o>​

</td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 32.35pt; height: 15.75pt;" valign="top" width="43">

3<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt; height: 15.75pt;" valign="bottom" width="78" nowrap="nowrap">

21930<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 0.75in; height: 15.75pt;" valign="bottom" width="72" nowrap="nowrap"></td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 32.35pt; height: 15.75pt;" valign="top" width="43">

4<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt; height: 15.75pt;" valign="bottom" width="78" nowrap="nowrap">

21980<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 0.75in; height: 15.75pt;" valign="bottom" width="72" nowrap="nowrap"></td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 32.35pt; height: 15.75pt;" valign="top" width="43">

5<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt; height: 15.75pt;" valign="bottom" width="78" nowrap="nowrap">

10915<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 0.75in; height: 15.75pt;" valign="bottom" width="72" nowrap="nowrap"></td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 32.35pt; height: 15.75pt;" valign="top" width="43">

6<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt; height: 15.75pt;" valign="bottom" width="78" nowrap="nowrap">

10845<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 0.75in; height: 15.75pt;" valign="bottom" width="72" nowrap="nowrap"></td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 32.35pt; height: 15.75pt;" valign="top" width="43">

7<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt; height: 15.75pt;" valign="bottom" width="78" nowrap="nowrap">

10755<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 0.75in; height: 15.75pt;" valign="bottom" width="72" nowrap="nowrap"></td> </tr> </tbody></table> <o> </o>
Lookup Table:
<table class="MsoNormalTable" style="width: 225.85pt; margin-left: 4.55pt; border-collapse: collapse;" width="301" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15.75pt;"> <td style="border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt medium; padding: 0in 5.4pt; width: 22pt; height: 15.75pt;" valign="top" width="29">

<o> </o>​

</td> <td style="border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0in 5.4pt; width: 63pt; height: 15.75pt;" valign="bottom" width="84" nowrap="nowrap">

A<o></o>​

</td> <td style="border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0in 5.4pt; width: 92.15pt; height: 15.75pt;" valign="bottom" width="123" nowrap="nowrap">

B<o></o>​

</td> <td style="border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0in 5.4pt; width: 48.7pt; height: 15.75pt;" valign="bottom" width="65" nowrap="nowrap">

C<o></o>​

</td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt medium; padding: 0in 5.4pt; width: 22pt; height: 15.75pt;" valign="top" width="29">

1<o></o>​

</td> <td style="border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0in 5.4pt; width: 63pt; height: 15.75pt;" valign="bottom" width="84" nowrap="nowrap">

Max<o></o>​

</td> <td style="border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0in 5.4pt; width: 92.15pt; height: 15.75pt;" valign="bottom" width="123" nowrap="nowrap">

Min<o></o>​

</td> <td style="border-style: solid solid none none; border-color: windowtext windowtext -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0in 5.4pt; width: 48.7pt; height: 15.75pt;" valign="bottom" width="65" nowrap="nowrap">

Index B<o></o>​

</td> </tr> <tr style="height: 15pt;"> <td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 22pt; height: 15pt;" valign="top" width="29">

2<o></o>​

</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15pt;" valign="bottom" width="84" nowrap="nowrap">

17702.5<o></o>​

</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15pt;" valign="bottom" width="123" nowrap="nowrap">

18000.5<o></o>​

</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

31<o></o>​

</td> </tr> <tr style="height: 15pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 22pt; height: 15pt;" valign="top" width="29">

3<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15pt;" valign="bottom" width="84" nowrap="nowrap">

17839.5<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15pt;" valign="bottom" width="123" nowrap="nowrap">

18137.5<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

32<o></o>​

</td> </tr> <tr style="height: 15pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 22pt; height: 15pt;" valign="top" width="29">

4<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15pt;" valign="bottom" width="84" nowrap="nowrap">

21220.75<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15pt;" valign="bottom" width="123" nowrap="nowrap">

21520.75<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

81<o></o>​

</td> </tr> <tr style="height: 15pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 22pt; height: 15pt;" valign="top" width="29">

5<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15pt;" valign="bottom" width="84" nowrap="nowrap">

21497.25<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15pt;" valign="bottom" width="123" nowrap="nowrap">

21822.25<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

82<o></o>​

</td> </tr> <tr style="height: 15pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 22pt; height: 15pt;" valign="top" width="29">

6<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15pt;" valign="bottom" width="84" nowrap="nowrap">

21798.75<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15pt;" valign="bottom" width="123" nowrap="nowrap">

22142.75<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

83<o></o>​

</td> </tr> <tr style="height: 15pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 22pt; height: 15pt;" valign="top" width="29">

7<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15pt;" valign="bottom" width="84" nowrap="nowrap">

22097.25<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15pt;" valign="bottom" width="123" nowrap="nowrap">

22397.5<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

84<o></o>​

</td> </tr> <tr style="height: 15pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 22pt; height: 15pt;" valign="top" width="29">

8<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15pt;" valign="bottom" width="84" nowrap="nowrap">

10696.25<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15pt;" valign="bottom" width="123" nowrap="nowrap">

10873.75<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

21<o></o>​

</td> </tr> <tr style="height: 15pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 22pt; height: 15pt;" valign="top" width="29">

9<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15pt;" valign="bottom" width="84" nowrap="nowrap">

10856.25<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15pt;" valign="bottom" width="123" nowrap="nowrap">

11053.75<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15pt;" valign="bottom" width="65" nowrap="nowrap">

22<o></o>​

</td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 22pt; height: 15.75pt;" valign="top" width="29">

10<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 63pt; height: 15.75pt;" valign="bottom" width="84" nowrap="nowrap">

11036.25<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 92.15pt; height: 15.75pt;" valign="bottom" width="123" nowrap="nowrap">

11193.75<o></o>​

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 48.7pt; height: 15.75pt;" valign="bottom" width="65" nowrap="nowrap">

23<o></o>​

</td> </tr> </tbody></table> <o> </o>
What should be the formula for column B at table 1?

 

[Yogi Anand]

Hi pkb68:

Welcome to MrExcel Board!

Unless I am missing something here shouldn't the value you are seeking 32

since 17950 lies between the values 17839.5 and 18137.5

Please clarify.

 

[pkb68]

Hi Yogi,

The value can be either 31 or 32 because there are overlappings of ranges in the Lookup table. 17950 is covered by both 31 and 32. Therefore, it is alright to have the value either 31 or 32. Thanks,

pkb68

 

[pd_it_guy]

I have almost the same question as previous poster; are the values in the second table supposed to be mutually exclusive, as they now overlap, which sort of defeats the lookup operation. Some values in the first apply to multiple rows.

How many rows do you foresee in each table- is it a fixed number as the example suggests, or can it expand.

Awaiting clarifications.

 

[pd_it_guy]

So what you want is, to be able to display ALL index numbers that may apply to the particular lookup value, correct? Would there ever be more than 2 for any given value?

 

[Yogi Anand]

Hi Yogi,

The value can be either 31 or 32 because there are overlappings of ranges in the Lookup table. 17950 is covered by both 31 and 32. Therefore, it is alright to have the value either 31 or 32. Thanks,

pkb68

Hi pkb68:

In that case ... one way:

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook
	A	B	C
1	Value Index A
2	17950	31
3	21930
4	21980
5	10915
6	10845
7	10755
8
9	Lookup Table:
10	Max	Min	Index B
11	17702.5	18000.5	31
12	17839.5	18137.5	32
13	21220.75	21520.75	81
14	21497.25	21822.25	82
15	21798.75	22142.75	83
16	22097.25	22397.5	84
17	10696.25	10873.75	21
18	10856.25	11053.75	22
19	11036.25	11193.75	23
Sheet9

</body></html>

 

[pkb68]

I really want to see only one value which ever match first, although there will be more than one match. Thanks,

pkb68

 

[pkb68]

Hi Yogi,

It worked perfectly! Thanks for the help. Regards,

Prajit

 

[Yogi Anand]

Yo Are Very Welcome Prajit ... now let us keep EXCELing.