Index/Match formula that returns blank/empty cells instead of #N/A and 1/0/1900

[heretolearnexcel]

I need to look up a value in another list and if the value is found then get the value from the same row in another column. If the value isn't found I need to get an empty value back, if the value is found but the value in the other column is empty I need to get a blank cell back, and since it's a date value if left alone I'll get 1/0/1900. I want to find a solution with a single index/match formula. There are basically three possible scenarios of values found:

1. The value is found and the date is found. With a normal index match I get a normal date value
2. The value is found but the date is empty. With a normal index match I get a 1/0/1900 date value
3. The value is not found. With a normal index match I get a #N/A value

Here is an example of the above

demo.xlsx
	A	B	C	D	E	F
1	List 1				List 2
2	id	date			id	date
3	123	5/1/2021			123	5/1/2021
4	12349	1/0/1900			1234	4/3/2021
5	12632	#N/A			12345
6					12346	1/1/2021
7					12347
8					12348
9					12349
10					12350
11					12351	3/2/2021
12					12352
13					12353
14					12354	2/28/2021
15					12355
16					12356	4/3/2021
17					12357
18					12358
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=INDEX($F$3:$F$18,MATCH(A3,$E$3:$E$18,0))

I think I basically need to include the formulas: ISERROR and ISBLANK within the index/match formula. But I can only find a way to include one of them at a time. I tried using an IFS formula but It doesn't have the "if false" part, which I think is needed. Example with ISERROR:

demo.xlsx
	A	B	C	D	E	F
1	List 1				List 2
2	id	date 1	comment		id	date 1
3	123	5/1/2021			123	5/1/2021
4	12349	1/0/1900	should get an empty cel		1234	4/3/2021
5	12632				12345
6					12346	1/1/2021
7					12347
8					12348
9					12349
10					12350
11					12351	3/2/2021
12					12352
13					12353
14					12354	2/28/2021
15					12355
16					12356	4/3/2021
17					12357
18					12358
Sheet1 (2)

Cell Formulas
Range	Formula
B3:B5	B3	=IF(ISERROR(INDEX($F$3:$F$18,MATCH(A3,$E$3:$E$18,0))),"",INDEX($F$3:$F$18,MATCH(A3,$E$3:$E$18,0)))

Example with ISBLANK:

demo.xlsx
	A	B	C	D	E	F	G
1	List 1				List 2
2	id	date 1	comment		id	date 1	comment
3	123	5/1/2021			123	5/1/2021
4	12349				1234	4/3/2021
5	12632	#N/A	should get an empty cel		12345
6					12346	1/1/2021
7					12347
8					12348
9					12349
10					12350
11					12351	3/2/2021
12					12352
13					12353
14					12354	2/28/2021
15					12355
16					12356	4/3/2021
17					12357
18					12358
19
Sheet1 (2)

Cell Formulas
Range	Formula
B3:B5	B3	=IF(ISBLANK(INDEX($F$3:$F$18,MATCH(A3,$E$3:$E$18,0))),"",INDEX($F$3:$F$18,MATCH(A3,$E$3:$E$18,0)))

Thank you in advance.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[heretolearnexcel]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done. Thanks for the suggestion. It's Excel 365.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	List 1				List 2
2	id	date 1	comment		id	date 1	comment
3	123	01/05/2021			123	01/05/2021
4	12349				1234	03/04/2021
5	12632		should get an empty cel		12345
6					12346	01/01/2021
7					12347
8					12348
9					12349
10					12350
11					12351	02/03/2021
12					12352
13					12353
14					12354	28/02/2021
15					12355
16					12356	03/04/2021
17					12357
18					12358
19
Summary

Cell Formulas
Range	Formula
B3:B5	B3	=LET(Return,XLOOKUP(A3,$E$3:$E$18,$F$3:$F$18,"",0),IF(Return="","",Return))

 

[heretolearnexcel]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	List 1				List 2
2	id	date 1	comment		id	date 1	comment
3	123	01/05/2021			123	01/05/2021
4	12349				1234	03/04/2021
5	12632		should get an empty cel		12345
6					12346	01/01/2021
7					12347
8					12348
9					12349
10					12350
11					12351	02/03/2021
12					12352
13					12353
14					12354	28/02/2021
15					12355
16					12356	03/04/2021
17					12357
18					12358
19
Summary

Cell Formulas
Range	Formula
B3:B5	B3	=LET(Return,XLOOKUP(A3,$E$3:$E$18,$F$3:$F$18,"",0),IF(Return="","",Return))

That works perfectly, actually. Is there a version of an index/match that could potentially work as well? I'm trying to learn/improve my inded/match skills. I know xlookup can work great many times, but other times it doesn't seem to work, in certain specifc scenarios.

 

[Fluff]

You can use

=LET(Return,INDEX($F$3:$F$18,MATCH(A3,$E$3:$E$18,0)),IF(ISNA(Return),"",IF(Return="","",Return)))

 

[heretolearnexcel]

You can use

=LET(Return,INDEX($F$3:$F$18,MATCH(A3,$E$3:$E$18,0)),IF(ISNA(Return),"",IF(Return="","",Return)))

Oh, of course. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.