Indexing Issues

[jim2012]

I have a workbook with 2 worksheet. On one we calculate the price for various kitchen layouts, we'll call it DATA. The second is basically a compacted Report of the data on the first. I have been using the Index() function paired with Match() so I only have to copy the Price per unit to the Report sheet. About five other value are pull across with Index() function and they are all on the same row on the data sheet.
The issue is when I have Price values that are the same, the index() of course pulls the first one, which may not be correct.
The Price Value I am coping from the DATA sheet has the correct row number for the other items, but how do I reference this row in my Index() or is there another function that will index to data with a know row and a column number?

[Paul]

Hi Jim,

Can you upload a copy of your workbook so we can see? Please include an example where there may be a duplicate price, and what your expected results should be. You can remove any confidential data, of course, but please be sure to include similar fake data of the same type and location.

Thanks!

[Dennis7849]

A sample of your workbook would help.

[jim2012]

Here is a sample

[jim2012]

Any Ideas? Anyone?

[darknation144]

The second argument in Index() is the row number how are you using index with out giving it a row? You can pass Index() rows and columns if you know where it is I don't see the problem.

[jim2012]

The problem is the row number using the Match() function. The Match () returns the first match it finds in the array, so if i have 2 prices that are the same(value I'm searching for) Match returns the first, which might not be the value I need. Is there a way to reference the row in Column H on the takeoff sheet? This is the value(Price) from the Kitchenette sheet and contains all the value I need to copy. I guess the other approach would be a dynamic array of some sort.

[darknation144]

Yes you can, Index(Table,Row,Column) You can use Match() again to give the correct column.

[jim2012]

OK, But can I reference the row from the H column in the Takeoff Sheet? the cell H9 on the Takeoff sheet contains the "= Kitchenttes!L28", the row I want to reference is Kichenette!28. Is there a way to reference this row with a function from Takeoff Sheet? The rows on Takeoff sheet are copied and inserted depending on the size of the project.

[darknation144]

You can use =address() to get the text and then delete the L

[jim2012]

I'm missing something here. How do I get Address() to return a text reference for the link in Takeoff Cell H9?

[darknation144]

I really don't think you need to use address() you will just have to use index with 2 matchs then +1 to either the column or row inside the index function.

[jim2012]

OK, You have lost me now, can you give an example?

[darknation144]

I would give an example but I don't understand your spreadsheet.

Say I search for a word Beds with in a matrix Match() can find me exact match within a column or row. If Shelves is in the cell next to it when I pass Match() to index() I do Match()+1. This is how I search for products with sub containers such as shelves draws etc. I'm sure you can apply the same teqnique but I don't understand what you mean by it returns the wrong price.

[jim2012]

I does not return the wrong "Price" because "Price" is the Look Up Value. The issue is when two units have the same price, but different Quantities or Notes. Match() chooses the first which may or may not be the correct row number in this case.

[darknation144]

Match.xlsx

So see the yellow line is this what you wanted? I just added a Totals+Component reference for Match to find the right column.

[darknation144]

Why would you search by price? You would surely only search by component number.

[jim2012]

I see what you mean, I think I can make it work.
Thanks