Help with IF and LOOKUP formulas

[Zeddie]

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.

 

[Toadstool]

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
	A	B	C	D	E	F	G	H
1	Sequence Begin	Sequence End	Code		Payment Number	VLOOKUP	INDEX & MATCH	AGGREGATE
2	P0000092	P0000332	AA00001		P0000700	AA00004	AA00004	AA00004
3	P0000352	P0000442	AA00002		P0001000	AA00005	AA00005	AA00005
4	P0000568	P0000628	AA00003		P0005200	AA00009	AA00009	AA00009
5	P0000650	P0000760	AA00004		P0007500	AA00013	AA00013	AA00013
6	P0000828	P0001218	AA00005		P0005150	No Match	No Match	No Match
7	P0002161	P0002311	AA00006		P0006600	No Match	No Match	No Match
8	P0002398	P0002698	AA00007		P0000352	AA00002	AA00002	AA00002
9	P0004168	P0004528	AA00008
10	P0005155	P0005485	AA00009
11	P0005826	P0005946	AA00010
12	P0006313	P0006583	AA00011
13	P0007160	P0007210	AA00012
14	P0007239	P0007749	AA00013
15	P0008030	P0008350	AA00014
16	P0008393	P0008843	AA00015
Sheet2

Cell Formulas
Range	Formula
F2:F8	F2	=IF(E2<=VLOOKUP(E2,$A$2:$B$16,2,TRUE),VLOOKUP(E2,$A$2:$C$16,3,TRUE),"No Match")
G2:G8	G2	=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:H8	H2	=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]

Hi Toadstool, brilliant thank you very much.

 

[Toadstool]

You're welcome!