I am making a sheet to pull data from a separate sheet based on user input. I have two named ranges to pull this data from based on a cells value. The front half of the formula works, but the back half returns an error of Col7 not found. I am sure I am missing something in plain sight. If there is an easier way to get the results I am after, I would appreciate the tip.
I have made a sample query range, to verify that the named range is working. The IFERROR statement has been removed from the back half of the formula so I could see the actual error. User input sheet Data tables sheet
>Solution :
Data4x10 does not start from column A (or B), so you can’t use Col7 because there is no such column in Data4x10
try:
={"Price"; ARRAYFORMULA(IF(B2:B="",,
IF(A2:A="4'x8'",IFERROR(VLOOKUP(B2:B, QUERY(
IMPORTRANGE("17lswFD_i79JMAUAN5qgvKZnIjXMJCANoQjWPZpgCkSA", "Plate & Sheet!Data4x8"), "SELECT Col1, Col3 WHERE Col1 IS NOT NULL", 0),2, FALSE)*D2:D,"N/A"), VLOOKUP(B2:B,QUERY(
IMPORTRANGE("17lswFD_i79JMAUAN5qgvKZnIjXMJCANoQjWPZpgCkSA", "Plate & Sheet!Data4x10"),"SELECT Col1, Col3 WHERE Col1 IS NOT NULL", 0),2, FALSE)*D2:D)))}
