Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP?
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 co=ADlumns "A" through "D". In column E I've managed to find a "COUNTIF" f=ADormula that would uniquely identify all of the different items in column=ADs "A" and "C", i.e no duplicates in new column E.... Here is where I get stuck. I need new columns "F" and "G" (=ADsample of desired output shown) to assign corresponding values from co=ADlumns "B" and "D", respectively. Such that, the values from column "B=AD" that have the same matching number up to the decimal point (from the a=ADdjacent cell in column "A") are paired up with the numbers in colum=ADn "D" which share the same number up to the decimal point (from the a=ADdjacent cell in column "C"). Therefore, columns "F" and "G" will yield the closest matchi=ADng values from the values in columns "B" and "D" that have the same number up to the decimal =ADpoint from their adjacent cells in columns "A" and "C", respectively. Actually, if it helps I ca=ADn lose the decimal point numbers. I think the example might better exp=ADlain 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP problem | Excel Worksheet Functions | |||
Vlookup, index, match? | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |