Referencing values from inside & outside a table in excel

[mtterry]

I have a table that gets updated based on a couple of data sources that are manipulated in the query editor in excel. That data is referenced by another tab for a process in my company. The table will fluctuate in # of rows, but shouldn't exceed 400. In the tab that my company uses, I want to insert a formula in certain columns that reference the data in the table. Before I used power query and referenced the table, I had the formulas in row #2 - row #400 that included an IF statement that would keep the rows blank if the spreadsheets being referenced didn't have data (i.e. the data was less than 400 rows). When I try to do that while referencing the tab that includes a table, I get a #VALUE error for the rows past the last row of the table, and I'm not sure how to have it just return blank cells?

 

[Yongle]

Try this method

Example original formula referencing the table (column A)
=VLOOKUP(Table1[@Friut],Table1,5,0)
Amended formula (column B)
=IFERROR(VLOOKUP(Table1[@Friut],Table1,5,0),"")

Book1
	A	B
1
2	17	17
3	30	30
4	24	24
5	#VALUE!
6	#VALUE!
7	#VALUE!
8	#VALUE!
9	#VALUE!
Sheet2

Cell Formulas
Range	Formula
A2:A9	A2	=VLOOKUP(Table1[@Friut],Table1,5,0)
B2:B9	B2	=IFERROR(VLOOKUP(Table1[@Friut],Table1,5,0),"")

 

[mtterry]

Try this method

Example original formula referencing the table (column A)
=VLOOKUP(Table1[@Friut],Table1,5,0)
Amended formula (column B)
=IFERROR(VLOOKUP(Table1[@Friut],Table1,5,0),"")

Book1
	A	B
1
2	17	17
3	30	30
4	24	24
5	#VALUE!
6	#VALUE!
7	#VALUE!
8	#VALUE!
9	#VALUE!
Sheet2

Cell Formulas
Range	Formula
A2:A9	A2	=VLOOKUP(Table1[@Friut],Table1,5,0)
B2:B9	B2	=IFERROR(VLOOKUP(Table1[@Friut],Table1,5,0),"")

That worked, thank you!