Help with IF and LOOKUP formulas

Zeddie

New Member
Joined
Feb 15, 2020
Messages
2
Office Version
365
Platform
Windows
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.
Sample.png
 

Toadstool

Active Member
Joined
Mar 5, 2018
Messages
463
Office Version
2016
Platform
Windows
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")
 

Forum statistics

Threads
1,085,744
Messages
5,385,625
Members
401,963
Latest member
Broccolikid

Some videos you may like

This Week's Hot Topics

Top