S
Steve
Hi,
It was suggested that I post this problem again with "VLOOKUP" in my
subject. I'm have the following sample of data from a worksheet in
columns "A" through "D". In column E I've managed to find a
"COUNTIF" formula that would uniquely identify all of the different
items in columns "A" and "C", i.e no duplicates in new column E....
Here is where I get stuck. I need new columns "F" and "G" (sample of
desired output shown) to assign corresponding values from columns "B"
and "D", respectively. Such that, the values from column "B" that
have the same matching number up to the decimal point (from the
adjacent cell in column "A") are paired up with the numbers in
column "D" which share the same number up to the decimal point (from
the adjacent cell in column "C"). Therefore, columns "F" and "G"
will yield the closest matching values from the values in columns "B"
and "D" that have the same number up to the decimal point from their
adjacent cells in columns "A" and "C", respectively. Actually, if it
helps I can lose the decimal point numbers. I think the example
might better explain what I need, because this was very difficult to
describe.
Example: If you look at the results in columns "F" and "G" that appear
next to the cells E8, E9, E10 & E11, which are all numbers that start
with the truncated number 166, THE LONE BLANK in cell G9 appears as the
blank because the only three values beside the 166#'s in column "C"
from column "D" (D8, D9 & D10) get matched up to the three closest
values of the four available 166# values in column "A" from column "B"
(B6, B7, B8 & B9). The closest matched values have to be the closest,
whether a smaller value or larger value. Of course this will need to
work both ways, because there may be blanks in column "F" as well.
Thanks,
Steve
Row/col A_______B_______C_______D_______E________F_______G
1 161.1 159 159.1 153 159.1 blank blank
2 161.2 339 159.2 334 159.2 blank blank
3 163.1 470 161.1 164 161.1 159 164
4 164.1 153 161.2 345 161.2 339 345
5 164.2 333 163.1 476 163.1 470 476
6 166.1 155 164.1 157 164.1 153 157
7 166.2 260 164.2 338 164.2 333 338
8 166.3 335 166.1 160 166.1 155 160
9 166.4 475 166.2 341 166.2 260 blank
10 170.1 336 166.3 481 166.3 335 341
11 172.1 158 170.1 161 166.4 475 481
12 172.2 338 170.2 342 170.1 336 161
13 174.1 471 172.1 163 170.2 blank 342
14 175.1 472 172.2 344 172.1 158 163
15 176.1 157 174.1 477 172.2 338 344
16 176.2 337 175.1 478 174.1 471 477
17 blank blank 176.1 162 175.1 472 478
18 blank blank blank blank 176.1 157 162
19 blank blank blank blank 176.2 337 blank
It was suggested that I post this problem again with "VLOOKUP" in my
subject. I'm have the following sample of data from a worksheet in
columns "A" through "D". In column E I've managed to find a
"COUNTIF" formula that would uniquely identify all of the different
items in columns "A" and "C", i.e no duplicates in new column E....
Here is where I get stuck. I need new columns "F" and "G" (sample of
desired output shown) to assign corresponding values from columns "B"
and "D", respectively. Such that, the values from column "B" that
have the same matching number up to the decimal point (from the
adjacent cell in column "A") are paired up with the numbers in
column "D" which share the same number up to the decimal point (from
the adjacent cell in column "C"). Therefore, columns "F" and "G"
will yield the closest matching values from the values in columns "B"
and "D" that have the same number up to the decimal point from their
adjacent cells in columns "A" and "C", respectively. Actually, if it
helps I can lose the decimal point numbers. I think the example
might better explain what I need, because this was very difficult to
describe.
Example: If you look at the results in columns "F" and "G" that appear
next to the cells E8, E9, E10 & E11, which are all numbers that start
with the truncated number 166, THE LONE BLANK in cell G9 appears as the
blank because the only three values beside the 166#'s in column "C"
from column "D" (D8, D9 & D10) get matched up to the three closest
values of the four available 166# values in column "A" from column "B"
(B6, B7, B8 & B9). The closest matched values have to be the closest,
whether a smaller value or larger value. Of course this will need to
work both ways, because there may be blanks in column "F" as well.
Thanks,
Steve
Row/col A_______B_______C_______D_______E________F_______G
1 161.1 159 159.1 153 159.1 blank blank
2 161.2 339 159.2 334 159.2 blank blank
3 163.1 470 161.1 164 161.1 159 164
4 164.1 153 161.2 345 161.2 339 345
5 164.2 333 163.1 476 163.1 470 476
6 166.1 155 164.1 157 164.1 153 157
7 166.2 260 164.2 338 164.2 333 338
8 166.3 335 166.1 160 166.1 155 160
9 166.4 475 166.2 341 166.2 260 blank
10 170.1 336 166.3 481 166.3 335 341
11 172.1 158 170.1 161 166.4 475 481
12 172.2 338 170.2 342 170.1 336 161
13 174.1 471 172.1 163 170.2 blank 342
14 175.1 472 172.2 344 172.1 158 163
15 176.1 157 174.1 477 172.2 338 344
16 176.2 337 175.1 478 174.1 471 477
17 blank blank 176.1 162 175.1 472 478
18 blank blank blank blank 176.1 157 162
19 blank blank blank blank 176.2 337 blank