So I have tried this a few ways but with no success, I am not sure if a
formula can do what I am asking for but here is my example. (A), (B) (C) ...
represent the columns in which the data is in.

Identifier (A) Top(B) Base(C) Unit (D)
42039027590000 4788 4797.0000 UNKNOWN
42039027590000 4887 4893.0000 MIOCENE
42039027600000 5095 5102.0000 MIOCENE
42039027610000 6783.05 6792.0000 FRIO
42039027620000 6683 6809.0005 FRIO
42039027650000 6550 6719.9995 FRIO
42039027660000 5148 5158.0000 MIOCENE
42039027660000 6568 6750.0000 FRIO
42039027680000 6547 6862.0000 FRIO
42039027690000 6790 6820.0005 FRIO
42039027710000 6758 6799.9995 FRIO
42039027720000 6818 6838.0000 FRIO

Identifier (H) Top(I) Base(J) Unit (K)
42039027590000 4788 4797 MIOCENE 4800
42039027590000 4887 4893 MIOCENE 4900
42039027600000 5095 5102 MIOCENE 5100
42039027610000 6783 6792 FRIO 6500-6800
42039027620000 6738 6754 FRIO 6500-6800
42039027620000 6783 6809 FRIO 6500-6800
42039027650000 6668 6676 FRIO 6500-6800
42039027660000 5085 5095 MIOCENE 5100 B
42039027660000 6570 6750 FRIO 6500-6800
42039027660001 5148 5158 MIOCENE 5150
42039027680000 6547 6800 FRIO 6500-6800
42039027690000 2358 3370 FRIO 7040
42039027690000 6790 6820 FRIO 6500-6800
42039027710000 6520 6550 FRIO 6500-6800
42039027710000 6738 6800 FRIO 6500-6800
42039027720000 6818 6838 FRIO 6500-6800

I need to update my Unit name with the items that are in column K. This is
what I would like to happen, have it look at the identifier and if they match
use the the depth range from Column B, and C and if that range is in the
range from column I, and J, plus or minus 4ft than use the unit from column K
to
place that unit in column E.

This is what it should like now.
Identifier (A) Top(B) Base(C) Unit (D) Comes
from col. K
42039027590000 4788 4797.0000 UNKNOWN MIOCENE 4800
42039027590000 4887 4893.0000 MIOCENE MIOCENE 4900
42039027600000 5095 5102.0000 MIOCENE MIOCENE 5100
42039027610000 6783.05 6792.0000 FRIO FRIO 6500-6800
42039027620000 6683 6809.0005 FRIO FRIO
42039027650000 6550 6719.9995 FRIO FRIO
42039027660000 5148 5158.0000 MIOCENE MIOCENE 5100 B
42039027660000 6568 6750.0000 FRIO FRIO 6500-6800
42039027680000 6547 6862.0000 FRIO FRIO
42039027690000 6790 6820.0005 FRIO FRIO 6500-6800
42039027710000 6758 6799.9995 FRIO FRIO
42039027720000 6818 6838.0000 FRIO FRIO 6500-6800

I have tried using vlookup with out success, it gets hung up because there
are duplicate identifiers so it looks for just the first same indentifier and
doesnt go past that.
This was the formula that I am working with but like I mention it gets
messed up when there are duplicate identifiers.
=IF(OR(VLOOKUP(A2,$H$2:$K$114,2,FALSE)=(B2+4),VLO OKUP(A2,$H$2:$K$114,2,FALSE)<=(B2-4),VLOOKUP(A2,$H$2:$K$114,3,FALSE)=(C2+4),VLOOKUP (A2,$H$2:$K$114,3,FALSE)<=(C2-4)),D2,VLOOKUP(A2,$H$2:$K$114,4,FALSE))

Hope someone can help, thanks

James wrote:
So I have tried this a few ways but with no success, I am not sure if a
formula can do what I am asking for but here is my example.

Take a good look at http://xldynamic.com/source/xld.SUMPRODUCT.html for
how to do multiple condition matches. Post back if you get stuck.