HLOOKUP + VLOOKUP?

[ekyles]

I have a data table with DISCOUNT (%) as the rows and SHARE (%) as the column. I would like to automatically lookup various combinations of discount and share to populate a chart. The chart input area only uses three of the possible 20 discount percentages and share multiples of five percentage points.

The chart input area needs to transpose the data - DISCOUNT as columns that I will populate depending on charts created with SHARE as the rows. What I would like to do is to lookup horizontally and vertically across the data table so whatever DISCOUNT I put in the chart input area it populates with the associated SHARE.

Hope that makes sense ... practically, the question is at a given discount level, the total purchases are worth X at different market share levels.

Thank you in advance.

 

[Somendra Misra]

Hi,

If you can post a sample file to explain your problem, that will be more help for the readers.

Regards,

 

[ekyles]

Thank you Somedra - been traveling, so a little tardy. The total data table is at the top, the inputs to the graph at the bottom. Currently hard coded, but I would like to dynamically change the "Rebate" numbers in the graph input to update the graph ad hoc based on rebate scenarios using the data table. Essentially an HLOOKUP+VLOOKUP.

For example, would like to scan the data table to find out what is the value of a 15% rebate at 5% market share (answer $181.4). Then dynamically change that to 50% and have the graph inputs and graph update.

Please see below:

 

[azumi]

Maybe better if not a picture, try to upload your sample workbook to see more detail the problem and avoid to typing the data again. With a fake data its no problem...

Cheers

 

[ekyles]

Here you go ... no idea what's going on with the graph BTW - makes no sense where the Y-axis values are coming from.

 

[Somendra Misra]

@ekyles

See the attached file.

Regards,

 

[ekyles]

@ekyles

See the attached file.

Regards,

Thank you. Will have to absorb, but I really appreciate your help!

 

[NARAYANK991]

Hi ,

The INDEX function can return an entire row or column if the relevant parameter is 0 ; thus :

=INDEX(Data , 1 , 0)

will return all the columns of the first row in the named range Data.

Similarly :

=INDEX(Data , 0 , 1)

will return all the rows of the first column in the named range Data.

Thus , if your data range C5:V24 is named as Data , then you can have your chart plotting the series generated by the named range whose formula would be : =INDEX(Data,MATCH('Data Table'!$C$28,Rebates,0),0)

I have assumed that your values in the range B29:B48 are the same as the values in the range C4:V4.

If so , just define a named range Rebates to refer to the range B5:B24.

Thus , the formulae and the range of values A29:E48 is not needed , since all of the chart series can be defined as named ranges. See the attached file , where one series has been plotted.

Narayan