VLOOKUP N/A# issue: Lookup_value is text (from INDIRECT formula), Table_array is number

[hfscook]

Thank you for reading.

I have 3 Sheets, Sheet1 contains table of data where rows are constantly being added (i.e., data is entered on row 2, when new data is needed to be entered, a row is inserted above and new data entered on row 2 with old data going to row 3).

Sheet2 cell C4 contains an INDRECT formula to pull data from a specific cell on Sheet1 Row 2 (always using the newest Row 2 data). Also on Sheet2 I have a VLOOKUP formula in cell D4 that uses C4 (the INDIRECT result) as the Lookup_value. The Table_array in this formula is on Sheet3 and is a cost schedule (e.g, up to 5 units, the cost is $30, up to 10 units the cost is $28, up to 20 units the cost is $26, etc.)

The problem: The Lookup_value is showing as text because it is refencing the INDIRECT result (e.g., "7"), the Table_array needs to be number because there may not be an exact number match (e.g., Up to 10 units, not exactly "7" units). This is resulting in a N/A# issue. I can't make the Lookup_value a number to match the array, and I can't make the array text because I may not have the exact match.

I could update my cost schedule to include every possible number of units, but seeing if there was a better option.

 

[Anthony47]

I can't make the Lookup_value a number to match the array, and I can't make the array text because I may not have the exact match.

Why you cannot transform the Lookup_Value to a number (using VALUE, eg VLOOKUP(VALUE(TheString), TheTable, etc etc))?

 

[hfscook]

Because I was hoping someone smarter than me could tell me to do that. Thank you, that worked perfectly.

 

[Anthony47]

Thank you for the feedback
Since you use Office365 you might use the smarter XLOOKUP function.
If you need to search not the exact match, then "play" with the 4th parametre of VLookup