Thread: Match Value

Dear All,

I am electrical engineer. Usually i work in field to connect cable.

but this time i "get" work above the desk. there is some formula that i still dont know. I want to know what formula to determine about cable.

but in this case. i give some simple data like below, and then let i develop related to my job by myself.

i have basic data like below :


2 A1
3 A2
4 B1
5 B2
6 B3

and if i have data of calculating like below :

0.1
2.3
2.8
2.9
3.3
3.5
5.1
2.3
3.4
5.9
etc...

how can i determine quickly.

if range
0-2 =A1 and 2=A1
2-3 =A2 and 3=A2
3-4 =B1 and 4=B1
4-5 =B2 and 5=B2
5-6 =B3 and 6=B3

so the result is :

0.1 = A1
2.3 = A2
2.8 = A2
2.9 = A2
3.3 = B1
3.5 = B1
5.1 = B3
2.3 = A2
3.4 = B1
5.9 = B3

thanks...

You could perhaps use LOOKUP

=LOOKUP(A1-0.01,{-0.01,2,3,4,5,6},{"A1","A2","B1","B2","B3"})

Where A1 holds the value.

Going forward please make sure you title your threads as succinctly as possible - given this was your first post I have revised this one for you.

Please also post your question in the relevant forum - I have moved this for you.

In short - read the Forum Rules before posting again.

Revise your file as follows:

F10:F13 should read 0 to 3 (not 1 to 4)

Add to row 14
F14: 4
G14: N/A

Formula in D10 either of:

=IF($C10="","",LOOKUP($C10-0.01,$F$10:$F$14,$G$10:$G$14))

or

=IF($C10="","",VLOOKUP($C10-0.01,$F$10:$G$14,2,TRUE))