Formula to find value (mapping)

[Hatye]

Hi,

I do have a worksheet with a list of a lot of account numbers, like this:

What I want to do, is to automatically insert a value in the D column, based on the account number (column A).


The mapping should be done using this layout:

Where the value I want to insert (in column D in first image) should be between one of the ranges (in column A and B), and then return the value in column C.

I need to have both from and to-value, as there might be some open gaps in the mapping scheme.


How should the formula be?

 

[shift-del]

Hatye,

from the data you posted I can't detect any gaps. So this would be enough.

[COLOR=#000000][FONT=Arial]=LOOKUP(A2,Sheet2!$A$2:$A$11,Sheet2!$C$2:$C$11)[/FONT][/COLOR]

And in case of gaps:

[COLOR=#000000][FONT=Arial]=LOOKUP(2,[/FONT][/COLOR][COLOR=#008000][FONT=Arial]1/(Sheet2!$A$2:$A$11<=A2)[/FONT][/COLOR][COLOR=#008000][FONT=Arial]/(Sheet2!$B$2:$B$11>=A2)[/FONT][/COLOR][COLOR=#000000][FONT=Arial],Sheet2!$C$2:$C$11)[/FONT][/COLOR]