HLookup using a cell value to set Table Array

[Artesis1]

Hi,
I'm trying to set up a series of HLookups for a template we use to handle our quotations, acknowledgements, invoices etc at work.

I've created the whole thing and have been asked to simplify how we search our reporting pricing.

I want to create product codes for how long each contract is:
3 Month Diagnostic Contract;
6 Month Diagnostic Contract;
12 Month Diagnostic Contract; and,
etc.

But we want to change the pricing based on the number of assets to be monitored and what the setup is for data being sent to our servers.

I wanted to use a list drop down for the data type that would match the table array range needed for the hlookup:
Type 1 = A3:I9;
Type 2 = A15:I21; and,
etc.
And then I wanted to use the number of units as the row to be looked in:
2-5 = 2;
6-10 = 3; and,
etc.

So I wrote the logic out in cells nearby so that people would understand the selections to be made in the drop downs.

And then wrote the following hlookup.
=IFERROR(HLOOKUP(C159,'QUOTE INPUT'!I62,'QUOTE INPUT'!I67,FALSE),0)

This gave me a 0 return which means that it didn't manage to use the references I gave it.

How do I fix this?

So that workers can input only in the quote input worksheet and have it find pricing in the reporting pricing worksheet, which has the 4 tables that contain all the diagnostic pricing. Where columns are number of months and rows are number of units, and each table overall is based on a setup type. It then needs to return this pricing to the product data worksheet where it gets feed into all the outputs I have in the excel spreadsheet.

I would like to avoid posting the spreadsheet if at all possible for two reasons, one it has company data, two I don't have permission to post it currently according to the details in the admin of this post.

Any help much appreciated!

Kind Regards

 

[steve the fish]

Try this:

=IFERROR(HLOOKUP(C159,INDIRECT('QUOTE INPUT'!I62),LOOKUP('QUOTE INPUT'!I67,{2,6},{2,3}),FALSE),0)

 

[Artesis1]

Hi Steve,
thanks for the swift reply,
I tried the code you used and still get a 0 as the response so something is still not quite right.

 

[steve the fish]

Delete the IFERROR. Does the formula actually error or is the result 0? What is in cells I62 and I67 of quote input sheet?

 

[Artesis1]

Says #N/A, I62 is currently set to be A3:I9 and I67 is set to be 2
Just tried changing I62 to be 'REPORTING PRICING!'A3:I9 and that changed error to be #REF

 

[steve the fish]

Try like this:

=IFERROR(HLOOKUP(C159,INDIRECT("'Reported Pricing'!"&'Quote input'!I62),LOOKUP('Quote input'!I67,{2,6},{2,3}),FALSE),0)

 

[Artesis1]

Tried your equation and adjusted it to remove the iferror to see if the 0 it returned was a fault or not.
Equation now looks like this:
=HLOOKUP(C159,INDIRECT("'Reporting Pricing'!" & 'QUOTE INPUT'!I62),LOOKUP('QUOTE INPUT'!I67,{2,6},{2,3}),FALSE)

It returned a value of 200, which is the correct value in that cell, but if I change the value of the dropdown in cell I67 the value in HLookup doesn't adjust.

So do I just need to replicate the indirect that we used for the table array and replace the lookup with it?

 

[Artesis1]

Steve thank you very much for your help I changed the Lookup to just be 'Quote Input'!I67 and the equation works perfectly.

It now reads =HLOOKUP(C159,INDIRECT("'Reporting Pricing'!" & 'QUOTE INPUT'!I62),'QUOTE INPUT'!I67,FALSE)

You've been really helpful and I've really appreciated it thank you!