Need help with lookup and comparing values

[Steve]

Hi,

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

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 matched up with the numbers in column "E" that
have the same number up to the decimal point and further aligned (in
the same row in columns "F" and "G") with the closest matching values
from column "D" that have the same number up to the decimal point from
the adjacent cell in column "C". 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.


Thanks,

Steve

A B C D E F G
161.1 159 159.1 153 159.1 blank......
161.2 339 159.2 334 159.2 blank......
163.1 470 161.1 164 161.1 159 164
164.1 153 161.2 345 161.2 339 345
164.2 333 163.1 476 163.1 470 476
166.1 155 164.1 157 164.1 153 157
166.2 260 164.2 338 164.2 333 338
166.3 335 166.1 160 166.1 155 160
166.4 475 166.2 341 166.2 260 blank
170.1 156 166.3 481 166.3 335 341
170.2 336 170.1 161 166.4 475 481
172.1 158 170.2 342 170.1 156 161
172.2 338 172.1 163 170.2 336 342
174.1 471 172.2 344 172.1 158 163
175.1 472 174.1 477 172.2 338 344
176.1 157 175.1 478 174.1 471 477
176.2 337 176.1 162 175.1 472 478
blank...........................176.1 157 162
blank...........................176.2 337 blank

 

[Ken]

Hi Steve,

Try this out.....

In F1 enter

=IF(ISERROR(VLOOKUP(E1,$A$1:$B$100,2,FALSE)),"",VLOOKUP(E1,$A$1:$B$100,2,FALSE))

Copy it down.

In G1 enter

=IF(ISERROR(VLOOKUP(E1,$C$1:$C$100,2,FALSE)),"",VLOOKUP(E1,$C$1:$D$100,2,FALSE))

Copy it down.

 

[Steve]

Thanks Ken for looking at this.

It seemed to work for column "F", however, I'm not seeing the results
that match what I have above for column "G". Any more ideas would be
appreciated. Steve

PS: I even tried changing the "$C$100" reference in the first part of
the column "G" formula to "$D$100"....

 

[Ken]

Sorry about the typo.

=IF(ISERROR(VLOOKUP(E1,$C$1:$D$100,2,FALSE)),"",VLOOKUP(E1,$C$1:$D$100,2,FALSE))

should work.

Do you get all blanks in column G?

If you do, my bet is that columns C and E are not both formatted as numeric
or text. They muts be formatted the same. To test this theory, in any blank
cells enter

=ISNUMBER(C1)
=ISNUMBER(E1)

If you don't get the same TRUE/FALSE, then you need to re-format your
columns to be the same.

 

[Steve]

Thanks,

This is a lot closer, however I don't think I did a very good job of
explaining what I really needed. If you look at the information that
ends up in cells G9 and G10, I need for this information to appear in
cells G10 and G11, respectively. The reason is that the value in the
cell G9 is a lot closer to the value in cell F10 and the value in the
in cell G10 is closer to the value in cell F11, thus leaving cell G9
blank.....I think my example ended up a little skewed because of the
text wrapping of the messages.

Kind Regards,

Steve

 

[Steve]

Hi,

I also forgot to put in my last explanation that the values in cells
F8, F9, F10 & F11 are all adjacent to a the 166#'s.


Regards,

Steve

 

[Ken]

Hi Steve,
I think that I finally understand your question. I don't see how it can work
using Excel functions. I would think that it would need a macro. However, I
am not an Excel pro. If the VLOOKUP's get you close, you might start a new
thread starting from the VLOOKUP results to make it easier for folks to
follow.

You might cinsider posting to the Excel Programming newgroup to see if
someone there could write up a macro for you.

Sorry I couldn't solve it for you.

 

[Steve]

Ken,

Thanks for taking the time to look at my problem. Sorry about not
being as clear in the beginning as I should have been. I'll follow
your advice and repost with VLOOKUP in my subject.


Regards,

Steve