Pulling Data based on Column and Row names

Are they always the same price, no matter what the colour?

I decided to actually read your post and got the answer on my own. Ha ha....

are the product codes all this format? (xxx.0000) Three letters, a period, and four numbers?

Assuming they are all that same format, the attached should work.
I added the grey cells to match the dimensions correctly.

Hi,

Thanks so much for the reply.
Your formula has taught me a lot about how indexing and matching works - I've spent the last few hours playing around with it.

I'm having a problem though - I can't seem to get it to finally function on my project here.


In short - I've changed MY item codes to be a concatenate of their design+color----> hence the concatenated Column I'm trying to use as one of the arrays for the lookup.
The Data I receive (And then added a concatenated column for an array) is on sheet 2

Here is a copy of what I'm trying to do. I need to fill in the yellow, highlighted cells.

Could someone tell me what's wrong with my formula? It looks like it should be looking up (for example) 116155, using that row for the index array, and then using the last 4 as the column matching up to the size array.

I don't see what I'm doing wrong...but I am a noob =\

#1 - the LEFT and RIGHT function can only be used on individual cells, not ranges.
To correct that, change your formula to the following and drag down:

Formula:

Please Login or Register  to view this content.

You then will receive error messages if there is not a match for your A1 values in column E and F of Sheet2.
There is no entries in column E of "116155", so it can't return a value.

Hi...
Thanks, but that didn't fix my formula and spit out the data....

Actually...in that data sheet I changed it so the MATCH left/Right was just one cell, like you said, but in another testing of this function a range of cells worked (though I defo see what you're saying here...)


What DID...fix my formula...was dragging down my concatenate and making it 116155.....I had manually entered 116155 thinking that maybe a concatenate was messing it up...

Why the heck is that the case? It doesn't help because it's still the exact same formula not functioning in my larger version (all products) of this.

An example is attached - You can see in Sheet2 Column E there is 116155 and 116232 in Cells E158 and E159 manually entered, if the formula above them is dragged down, then my formula on sheet one works...despite the values being the same :S


Edit- Update - My Formula is working on my Main sheet now - What you said combined with dragging the concatenate formula down seemed to work.....


A last question, if anyone happens to know -
Some of my product codes are 6 characters, some are 7, some are 5. All the sizes are 4 (So the size array match works great)

Naturally, that causes problems....I thought match type would help but with no real rhyme or reason to product codes, there's no sense of ascending/descending.

The reason it wasn't working unless the value in column E was a formula is because the 116155 in sheet1 was formatted as "TEXT", and 116155 in sheet 2 (when manually entered) was formatted as "NUMBER". That is why it couldn't find a match. the formatting of the text matters when using the MATCH function.

In regards to your second question, I am not sure what your question is. What do you mean by "causes problems"?
You also have no headers on any of your columns. Which column is your product code? Column A, Column B?
Can you say specifically:
1. What is occurring.
2. What you want to occur.