Lookup Value from different tables (references)

[zinah]

Hi,

I have below table that I need to fill the Min-Max ranges from a different 2 tables/references based on ID. I have added the Expected results column but I need a formula that can give me these results.

From below data: if ID is found in Range 1 table then give me the min & max for this ID in this format (Min-Max), if ID is not found in Range 1 then check Range 2 table and give me the results. since min and max in Range 2 table are all n/a then the result for any ID is n/a and if ID is not found in both ranges the expected result is n/a as well.

Test File_LookupValue.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ID	Range Type	Expected Results (Min-Max)
2	034	Range 1	16% - 20%					Range 1					Range 2
3	035	Range 1	17% - 21					ID	Min	Mid	Max		ID	Min	Mid	Max
4	036	Range 1	18% - 22%
5	037	Range 1	19% - 23%					003	5%	7%	9%		040	n/a	n/a	n/a
6	030	Range 1	14% - 18%					005	6%	8%	10%		041	n/a	n/a	n/a
7	031	Range 1	15% - 19%					006	7%	9%	11%		042	n/a	n/a	n/a
8	040	Range 2	n/a					007	8%	10%	12%		043	n/a	n/a	n/a
9	071	Range 2	n/a					008	9%	11%	13%		044	n/a	n/a	n/a
10	072	Range 2	n/a					009	10%	12%	14%		045	n/a	n/a	n/a
11	073	Range 2	n/a					010	11%	13%	15%		046	n/a	n/a	n/a
12	074	Range 2	n/a					011	12%	14%	16%		047	n/a	n/a	n/a
13	035	Range 1	17% - 21					012	13%	15%	17%		048	n/a	n/a	n/a
14	036	Range 1	18% - 22%					030	14%	16%	18%		049	n/a	n/a	n/a
15	068	Range 2	n/a					031	15%	17%	19%		068	n/a	n/a	n/a
16	076	Range 1	20% - 24%					034	16%	18%	20%		069	n/a	n/a	n/a
17	077	Range 1	21% - 25%					035	17%	19%	21%		070	n/a	n/a	n/a
18	071	Range 2	n/a					036	18%	20%	22%		071	n/a	n/a	n/a
19	097	NA	n/a					037	19%	21%	23%		072	n/a	n/a	n/a
20	098	NA	n/a					076	20%	22%	24%		073	n/a	n/a	n/a
21								077	21%	23%	25%		074	n/a	n/a	n/a
22
23
Sheet1

Cell Formulas
Range	Formula
J5:K21	J5	=I5+0.02
I6:I21	I6	=I5+0.01
B2:B20	B2	=IF(ISNUMBER(XMATCH(A2,$H$5:$H$21,0)),$H$2,IF(ISNUMBER(MATCH(A2,$M$5:$M$21,0)),$M$2,"NA"))

 

[Misca]

Try something like:

=IFERROR(
TEXT(VLOOKUP($B3,$I$6:$L$22,2,0),"0 % -")&TEXT(VLOOKUP($B3,$I$6:$L$22,4,0),"0 %"),
IF(
VLOOKUP($B3,$N$6:$Q$22,2,0)="n/a",
"n/a",
TEXT(VLOOKUP($B3,$N$6:$Q$22,2,0),"0 % -")&TEXT(VLOOKUP($B3,$N$6:$Q$22,4,0),"0 %")
)
)

I tried to format the formula along the lines of DAX to make it easier to read / understand. It's actually pretty simple when you look at it: It's basically just two basic VLOOKUPs wrapped in an IFERROR. And then the VLOOKUPs are wrapped inside of a TEXT formula to show the results as a percentage. And then there's an additional IF that checks if the second set of VLOOKUPs returns "n/a" to return simply "n/a". Without it you'd get "n/an/a". Also, if your "n/a"s are actual N/A errors then you might want to use IFNA instead of the text "n/a" I'm using here.

Also, make sure the ranges match your data: I copied your data and pasted it to my workbook and didn't make sure the ranges match.

 

[zinah]

Try something like:

=IFERROR(
TEXT(VLOOKUP($B3,$I$6:$L$22,2,0),"0 % -")&TEXT(VLOOKUP($B3,$I$6:$L$22,4,0),"0 %"),
IF(
VLOOKUP($B3,$N$6:$Q$22,2,0)="n/a",
"n/a",
TEXT(VLOOKUP($B3,$N$6:$Q$22,2,0),"0 % -")&TEXT(VLOOKUP($B3,$N$6:$Q$22,4,0),"0 %")
)
)

I tried to format the formula along the lines of DAX to make it easier to read / understand. It's actually pretty simple when you look at it: It's basically just two basic VLOOKUPs wrapped in an IFERROR. And then the VLOOKUPs are wrapped inside of a TEXT formula to show the results as a percentage. And then there's an additional IF that checks if the second set of VLOOKUPs returns "n/a" to return simply "n/a". Without it you'd get "n/an/a". Also, if your "n/a"s are actual N/A errors then you might want to use IFNA instead of the text "n/a" I'm using here.

Also, make sure the ranges match your data: I copied your data and pasted it to my workbook and didn't make sure the ranges match.

Thank you so much for your suggested solution and for taking time and explain your formula. I adjusted the formula by adding IFNA as I got #N/A for the IDs that are not found in both ranges, another issue I have is what about if I have "check" as it's showing in below table, I need to get the result "check" if I have such incident rather than the current situation "checkcheck", can you help please?

Test File_LookupValue.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ID	Range Type	Expected Results (Min-Max)	Suggested Formula
2	034	Range 1	16% - 20%	16 % -20 %				Range 1					Range 2
3	035	Range 1	17% - 21	17 % -21 %				ID	Min	Mid	Max		ID	Min	Mid	Max
4	036	Range 1	18% - 22%	18 % -22 %
5	037	Range 1	19% - 23%	19 % -23 %				003	5%	7%	9%		040	n/a	n/a	n/a
6	030	Range 1	14% - 18%	14 % -18 %				005	6%	8%	10%		041	n/a	n/a	n/a
7	031	Range 1	15% - 19%	15 % -19 %				006	7%	9%	11%		042	n/a	n/a	n/a
8	040	Range 2	n/a	n/a				007	8%	10%	12%		043	n/a	n/a	n/a
9	071	Range 2	n/a	n/a				008	9%	11%	13%		044	n/a	n/a	n/a
10	072	Range 2	n/a	n/a				009	10%	12%	14%		045	n/a	n/a	n/a
11	073	Range 2	n/a	n/a				010	11%	13%	15%		046	n/a	n/a	n/a
12	074	Range 2	n/a	n/a				011	12%	14%	16%		047	n/a	n/a	n/a
13	035	Range 1	17% - 21	17 % -21 %				012	13%	15%	17%		048	n/a	n/a	n/a
14	036	Range 1	18% - 22%	18 % -22 %				030	14%	16%	18%		049	n/a	n/a	n/a
15	068	Range 2	n/a	n/a				031	15%	17%	19%		068	n/a	n/a	n/a
16	076	Range 1	20% - 24%	20 % -24 %				034	16%	18%	20%		069	n/a	n/a	n/a
17	077	Range 1	21% - 25%	CheckCheck				035	17%	19%	21%		070	n/a	n/a	n/a
18	071	Range 2	n/a	n/a				036	18%	20%	22%		071	n/a	n/a	n/a
19	097	NA	n/a	n/a				037	19%	21%	23%		072	n/a	n/a	n/a
20	098	NA	n/a	n/a				076	20%	22%	24%		073	n/a	n/a	n/a
21								077	Check	Check	Check		074	n/a	n/a	n/a
22
23
24
25
Sheet1

Cell Formulas
Range	Formula
J5:K20	J5	=I5+0.02
I6:I20	I6	=I5+0.01
B2:B20	B2	=IF(ISNUMBER(XMATCH(A2,$H$5:$H$21,0)),$H$2,IF(ISNUMBER(MATCH(A2,$M$5:$M$21,0)),$M$2,"NA"))
D2:D20	D2	=IFNA(IFERROR( TEXT(VLOOKUP($A2,$H$5:$K$21,2,0),"0 % -")&TEXT(VLOOKUP($A2,$H$5:$K$21,4,0),"0 %"), IF( VLOOKUP($A2,$M$5:$P$21,2,0)="n/a", "n/a", TEXT(VLOOKUP($A2,$M$5:$P$21,2,0),"0 % -")&TEXT(VLOOKUP($A2,$M$5:$P$21,4,0),"0 %") ) ),"n/a")

 

[Misca]

You can solve the Check problem by adding yet another IF check; "IF the VLOOKUP results "Check" then return just the first "Check" otherwise return the double VLOOKUPs". I believe you can come up with the correct formula since it's quite similar to the one in my original formula: Basically all you need to do is replace the "n/a":s with "Check".

 

[zinah]

You can solve the Check problem by adding yet another IF check; "IF the VLOOKUP results "Check" then return just the first "Check" otherwise return the double VLOOKUPs". I believe you can come up with the correct formula since it's quite similar to the one in my original formula: Basically all you need to do is replace the "n/a":s with "Check".

Thank you so much for your help, sorry I thought I replied to your comment.