Return Number When Entry is in a Range of Two Columns

[wmunsey81]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]1
[/TD]
[TD]1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]16[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]28[/TD]
[TD]41[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]42[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]54[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]67[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]79[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]92[/TD]
[TD]103[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]104[/TD]
[TD]116[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]117[/TD]
[TD]128[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]129[/TD]
[TD]141[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]142[/TD]
[TD]153[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]154[/TD]
[TD]166[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]167[/TD]
[TD]178[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]179[/TD]
[TD]191[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]192[/TD]
[TD]203[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]204[/TD]
[TD]216[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]217[/TD]
[TD]228[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]229[/TD]
[TD]241[/TD]
[/TR]
</tbody>[/TABLE]































Looking for a formula for when an entry is a number... lets say "43", it will look in the range of column B & C and give you its corresponding number in column A. So in this case, it would return "4". If "0" was entered, it would return 0 and if "13" was entered in the cell, "1" would be returned.

Thank you in advance!

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G
1	1	1	15
2	2	16	27			0	0
3	3	28	41
4	4	42	53
5	5	54	66
6	6	67	78
7	7	79	91
8	8	92	103
9	9	104	116
10	10	117	128
11	11	129	141
12	12	142	153
13	13	154	166
14	14	167	178
15	15	179	191
16	16	192	203
17	17	204	216
18	18	217	228
19	19	229	241
Doc1

Cell Formulas
Range	Formula
G2	=IFERROR(INDEX(A1:A19,MATCH(F2,B1:B19,1)),0)

 

[Joe4]

Another alternative.

Do you have the ability to move the values in column A so they appear in the third column in your table, rather than the first?
If so, then you can do this pretty easily using VLOOKUP, making use of the Approximate match argument.

See: https://exceljet.net/tips/how-to-use-vlookup-for-approximate-matches
and https://exceljet.net/formula/calculate-grades-with-vlookup

 

[wmunsey81]

How about

Excel 2013/2016

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]128[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]166[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]178[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]179[/TD]
[TD="align: right"]191[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]192[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]216[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]217[/TD]
[TD="align: right"]228[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]229[/TD]
[TD="align: right"]241[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Doc1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IFERROR(INDEX(A1:A19,MATCH(F2,B1:B19,1)),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes, that worked exactly correct with all test. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback