# Help with IF and LOOKUP formulas

#### Zeddie

##### New Member
Hi, I am looking for help to create a formula to return a value where its appears between two value ranges from two columns of data. I have attached an example which will explain this better, I think I should use IF and Vlookup but not sure how to build this. Hope the sample makes sense and thank you for your help. ##### Active Member
Hi Zeddie,

Let me give you three ways.

(1) VLOOKUP: Because your Sequence Begin is in ascending sequence then you can use VLOOKUP with an approximate match to retrieve a Sequence End and if that is equal or greater than the Payment Number then use another VLOOKUP to retrieve the Code.

(2) INDEX & MATCH: Basically the same approach as VLOOKUP and requires Sequence Begin to be in ascending sequence. INDEX & MATCH are often a better approach as they let you do more, e.g. they could retrieve Code if it was in a column left of Sequence Begin.

(3) AGGREGATE: This works if Sequence Begin is not in order as it looks down every row and retrieves the first one that satisfies the criteria, that Payment Number is between Sequence Begin and Sequence End.

Zeddie.xlsx
ABCDEFGH
1Sequence BeginSequence EndCodePayment NumberVLOOKUPINDEX & MATCHAGGREGATE
2P0000092P0000332AA00001P0000700AA00004AA00004AA00004
3P0000352P0000442AA00002P0001000AA00005AA00005AA00005
4P0000568P0000628AA00003P0005200AA00009AA00009AA00009
5P0000650P0000760AA00004P0007500AA00013AA00013AA00013
6P0000828P0001218AA00005P0005150No MatchNo MatchNo Match
7P0002161P0002311AA00006P0006600No MatchNo MatchNo Match
8P0002398P0002698AA00007P0000352AA00002AA00002AA00002
9P0004168P0004528AA00008
10P0005155P0005485AA00009
11P0005826P0005946AA00010
12P0006313P0006583AA00011
13P0007160P0007210AA00012
14P0007239P0007749AA00013
15P0008030P0008350AA00014
16P0008393P0008843AA00015
Sheet2
Cell Formulas
RangeFormula
F2:F8F2=IF(E2<=VLOOKUP(E2,\$A\$2:\$B\$16,2,TRUE),VLOOKUP(E2,\$A\$2:\$C\$16,3,TRUE),"No Match")
G2:G8G2=IF(INDEX(\$B\$2:\$B\$16,MATCH(E2,\$A\$2:\$A\$16,1))>=E2,INDEX(\$C\$2:\$C\$16,MATCH(E2,\$A\$2:\$A\$16,1)),"No Match")
H2:H8H2=IFERROR(INDEX(\$C\$2:\$C\$16,AGGREGATE(15,6,ROW(\$A\$2:\$A\$16)-ROW(\$A\$1)/((\$A\$2:\$A\$16<=E2)*(\$B\$2:\$B\$16>=E2)),1)),"No Match")

#### Zeddie

##### New Member
Hi Toadstool, brilliant thank you very much.

• You're welcome!