There are two cost tables in the worksheet, one for Vendor A and one for Vendor B.
Both tables are defined as thenamed rangesvendor_a(B5:C8) andvendor_b(B11:C14).
However, the formula below will fail with a #VALUE!
error:
Why is that?
The formula above messes up thanks to Excel interprets the table as atext value, not arange.
What we need is a way to tell Excel to interpret the text value like a cell reference.
This is a job for theINDIRECT functionwhich is designed to evaluate a text value as a reference.
If the vendor is changed to “b”, VLOOKUP will dynamically switch tables and return $12.50.
It also nicely demonstrates how one function can benestedinside another to deliver a range instead of a single value.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
More than one condition can be tested by nesting IF functions.
The IF…
Related videos
How to use VLOOKUP
