Lookup using Multiple criteria in Excel/VBA

[arunt_28]

Hi All,

[TABLE="width: 394"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD][TABLE="width: 394"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]I'm trying to solve the below problem, Can any experts help me to solve it, Thank You

In the below table, if i enter X,Y&Z i need the corresponding Node which is near to this X,Y& Z values. Example if Enter X= 8900, Y=1000,Z=1000, the output should be Node:2

[TABLE="width: 394"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Node[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8800[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16700[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]24500[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]10700[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]24500[/TD]
[TD]1000[/TD]
[TD]10700[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]20050[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8800[/TD]
[TD]1000[/TD]
[TD]20050[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]16700[/TD]
[TD]1000[/TD]
[TD]20050[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]24500[/TD]
[TD]1000[/TD]
[TD]20050[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1000[/TD]
[TD]6000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]8800[/TD]
[TD]6000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]16700[/TD]
[TD]6000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]24500[/TD]
[TD]6000[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1000[/TD]
[TD]6000[/TD]
[TD]10700[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]24500[/TD]
[TD]6000[/TD]
[TD]10700[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]1000[/TD]
[TD]6000[/TD]
[TD]20050[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]8800[/TD]
[TD]6000[/TD]
[TD]20050[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]16700[/TD]
[TD]6000[/TD]
[TD]20050[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]24500[/TD]
[TD]6000[/TD]
[TD]20050[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gary McMaster]

Welcome to the board!

I don't think you given enough info for us to help you.

Is it always just X that deviates from the values in the list or can the deviation be with Y and Z as well?
Can the deviation be in more than one of the 3 columns at the same time?
Are you trying to do this with formulas or VBA code?

At first glance, I would say sum X,Y,Z in column "E" and compare that to the sum of the X, Y, Z that you enter. That should narrow the list
substantially. Maybe if you subtract one sum from the other the smallest absolute value will be your solution. I guess it depends on the answers to the questions above.

Gary