Referencing values from inside & outside a table in excel

mtterry

New Member
Joined
Aug 9, 2016
Messages
45
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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,974
Office Version
  1. 365
Platform
  1. Windows
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),"")

Cell Formulas
RangeFormula
A2:A9A2=VLOOKUP(Table1[@Friut],Table1,5,0)
B2:B9B2=IFERROR(VLOOKUP(Table1[@Friut],Table1,5,0),"")
 

mtterry

New Member
Joined
Aug 9, 2016
Messages
45
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),"")

Cell Formulas
RangeFormula
A2:A9A2=VLOOKUP(Table1[@Friut],Table1,5,0)
B2:B9B2=IFERROR(VLOOKUP(Table1[@Friut],Table1,5,0),"")
That worked, thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top