Find a value in 2-column array

[asallwey]

I'm having brain fade and need help. I have a list of drills, coded as to type, in column A, in B is their decimal size. Users don't always know the size, but do know the code. The code field is formatted as Text because of the coding. The array is sorted in decimal order (numeric format).

The use is to enter the drill code and have the decimal size retrieved. I think I've done this before but just have a road block.

Here is the coding:
# 00 = #'d Drills
ms12 = machine screws ( 12 in this case)
M00 = metric drills
1/2 = fractional drills

Here is sample data:
col A Col B
ms0 0.0600
1/16 0.0625
M1.6 0.0630
#52 0.0635
#51 0.0670
#50 0.0700
ms1 0.0730

Thanks, Alex

 

[kweaver]

You want a VLOOKUP or (if you have 365) an XLOOKUP.

 

[Fluff]

How about

=VLOOKUP(F2,A2:B8,2,0)

where F2 is the cell you enter the code into.

 

[asallwey]

How about

=VLOOKUP(F2,A2:B8,2,0)

where F2 is the cell you enter the code into.

Started there, but Col A is not ascending or descending sorted, so it doesn't come up with the right answer.

 

[Fluff]

The data doesn't need to be sorted.

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	ms0	0.06				#51	0.067
3	1/16	0.0625				ms0	0.06
4	M1.6	0.063				ms1	0.073
5	#52	0.0635				m1.6	0.063
6	#51	0.067				1/16	0.0625
7	#50	0.07
8	ms1	0.073
9
Data

Cell Formulas
Range	Formula
G2:G6	G2	=VLOOKUP(F2,$A$2:$B$8,2,0)

 

[asallwey]

Well, I can't explain it. I went back to my sheet after reading your reply and it was working. I played with it several time before posting and it wouldn't work. It's working now.

Thanks for helping! Alex

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[asallwey]

I just found out why it didn't work for me! I didn't include the 4th element of the formula, it needed False or 0 to retrieve the correct answer. I guess the last few times I've used vlookup I didn't need it, and this time my brain didn't see it, even when I looked at your example. Tunnel vision can add quite a challenge sometimes! Thanks again! Alex