# Pulling Data based on Column and Row names

1. ## Pulling Data based on Column and Row names

First of all, I appreciate the help I receive from this forum - It's a great tool I get to use for work and I'm happy to learn from you all!

I am trying to help someone with updating their inventory information quicker.

I have the vendor's pricing from their excel sheet organized like so

"Design" "Color" 9x12, 10x16, 13x18, etc....
ABC RED 500 600 800
Blue 500 600 800

Essentially, I have Product Codes based on design and size. all the colors are the same prices. They also give me product code info in another column...

So my Excel sheet, I have product code in one column
(ABC.0912 To represent a 9x12 sized item of ABC design)
Like so

Product code Price
ABC.0912 500
ABC.1016 600
ABC.1318

So right now I'm manually entering....I would index it but they don't really line up right, and we don't carry all the products.
So I'm wondering if there's a way to somehow drag the prices into the data correctly without manually doing it? I'll eventually re-organize everything probably if it can't be done.

I thought maybe Fuzzylookup module? But I realized I can fuzzylookup the correct Column (ABC), but couldn't get a connection between the 9x12 column name and the .0912 product code.

Can I somehow search In one column for a code, and then in turn search the row and spit out the corresponding crossing point?

2. ## Re: Pulling Data based on Column and Row names

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

4. ## Re: Pulling Data based on Column and Row names

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

5. ## Re: Pulling Data based on Column and Row names

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

6. ## Re: Pulling Data based on Column and Row names

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 =\

7. ## Re: Pulling Data based on Column and Row names

#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.

8. ## Re: Pulling Data based on Column and Row names

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.

9. ## Re: Pulling Data based on Column and Row names

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1