INDEX / MATCH Formula Errors

[Ben]

I recently posted up a question as to which type of formula I should use to
achieve what I want the workbook to do.

The workbook actually creates a quote based around the sale of USB products.
Basically, when I have selected what size (capacity) that I want to quote, I
then type in the quantity. From here, the formula then needs to look up what
capacity I am offering in the capacity cell and then the quantity in the
quantity cell before looking to a cell range which shows quantity breaks (ie.
1 column for 100pcs, 2nd column is 250pcs) and then the price is then
calculated.

So, for example, if I select a 128MB device, and the customer wants 200pcs,
it should look up within the cell range and see that 200 comes between the
column of 100pcs and 250pcs and offer what is in the 100pc column.

Currently, the formula that is in the cell is as follows:
=INDEX(AM19:AS19,MATCH(B13,AM20:AS28,0),MATCH(AM19:AS28,0))

A guide to the cells are:

AM19:AS19 = Quantity Break Column Heads
B13 = Capacity Cell
AM20:AS28 = Various prices depending on the columns
B19 = Quantity Cell

For some reason the formula will not work and I have beta'd numerous ways to
try and solve it but cannot.

Can anyone submit any suggestions as to how I can solve this ??

Many Thanks,
Ben

 

[Bob Phillips]

It is going to be something like

=INDEX(AM19:AS28,MATCH(B13,AM19:AS19,1),MATCH(B19,AM20:AS20,1))

but the data layed out would help clarify it fully.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ben]

Hi Bob,

Thanks for that.
What do you mean by the data being layed out therefore clarifying it ??

I have put that formula in, selected a capacity and then typed in a quantity
but it still comes up with #N/A

Thanks,
Ben

 

[Bob Phillips]

I mean to present a sample of the data in a message, cells addresses and
values, and tell us what the expected result should be.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ben]

OK. Basically, once I've selected the capacity and entered in the required
quantity, the cell should look to this cell range - here I have copied the
first 2 rows / columns:

100 250 500 1000 1500 2500 5000
32MB £0.01 £0.02 £0.03 £0.04 £0.05 £0.06 £0.07

So, basically, based on the capacity selected (32mb) and the quantity
entered (which maybe exactly those in the row headings, or in between), it
should display what is in the the corresponding cell underneath (so for
100pcs, 0.01 or for 275pcs, 0.02 as its based on the 250pc price)

Hopefully this helps and you can advise me some suggestions !

Many Thanks,
Ben

 

[Bob Phillips]

This worked for me

=INDEX(A1:H20,MATCH(L1,A1:A20,0),MATCH(M1,A1:H1,1))

you will need to adjust the cells


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ben]

Hi Bob,

Thanks for that - I tried replicating this but just need to know which cells
reflect what in the formula you did so I know which ones to adjust in the
actual workbook itself.

Thanks,
Ben

 

[Bob Phillips]

Ben,

Here is a stab with your cells

=INDEX(AM19:AS28,MATCH(B13,AM19:AM28,0),MATCH(B19,AM19:AS19,1))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ben]

Hi Bob,

Have had a crack with that and its still coming up with #N/A
When I go into "Show Calculation Steps", this section is underlined with the
entry in cell B13 underlined to what - MATCH(B13,AM19:AM28,0)

Any ideas ??

KR
Ben

 

[Bob Phillips]

If the data is as you laid out, if B13 holds 32Mb say and B19 holds 800 say,
it works ... at least it does for me.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ben]

Hi Bob,

My data is laid out exactly as suggested but it still isn't returning
anything !!

Have to admit, I'm seriously scratching my head !!

Anything else I could try ??

Cheers,
Ben

 

[Bob Phillips]

As I said, it works fine for me, so I'm out of ideas.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Maybe it's time for you to write down:

1. The address of the table.
2. The address of the column that contains the headers for the rows.
3. The address of the cell that has to match one of the row headers.
4. The address of the row that contains the headers for the columns.
5. The address of the cell that has to match one of the column headers.

 

[Ben]

Hi Dave / Bob,

I have since tried this instead -

I created a drop-down box with a cell link which in this case is cell V19.
Depending on what I select it comes up with anything from 1 to 9 so I then
created this formula
=IF(V19=1,INDEX(AM20:AS20,B19) and I have then copied this for each time V19
equals 1 to 9. However, once it gets to V19=7, it comes up with problems !

B19 is where the quantity is typed in.

I then do a test run and select it so V19 does equal 1 and type in quantity
of 100 and it changes from "FALSE" to #REF.

What do you think I maybe doing wrong here ??

Cheers,
Ben

 

[Dave Peterson]

That doesn't look like the formula that Bob posted.

Is there a reason you checked V19, but used B19 in the =index() portion.

And if you're copying the formula, you may want to use $am$20:$as$20 so that
this range won't change.

 

[Ben]

V19 displays numbers 1 to 9 depending on what is selected inthe drop down box
B19 is where the quantity is entered into.

 

[Dave Peterson]

It still doesn't look like the formula that Bob suggested.

 

[Dave Peterson]

And I've lost track of what you want to do.

It still doesn't look like the formula that Bob suggested.

<<snipped>>