LinkBack URL
About LinkBacks
I am trying to convert a table into three columns so that I can use the data in a vlookup. Any suggestions would be appreciated.
I am trying to convert a table into three columns so that I can use the data in a vlookup. Any suggestions would be appreciated.
Last edited by panamajack; 08-27-2009 at 06:52 PM.
you can use the table as-is if you wish:
The formula in C14 is =INDEX($A$2:$M$11, MATCH(A14, $A$2:$A$11, 0), MATCH(B14, $A$2:$M$2, 0) )
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
shg,
Thanks for replying so quickly, I appreciate it.
Using the solution you propose, how do I get the data into the cells from A14 and B14? Will I have to populate it manually?
My point was, unless there is some second purpose to make a linear list, you can use a 2D lookup directly from the existing table.
I need to use the data in the table to verify that a second list of data is complete, so I think I do need to put the data in the table into a linear list.
A14: =INDEX($A$3:$A$11, INT( (ROWS(A$14:A14) - 1) / COLUMNS($B$2:$M$2) ) + 1)
b14: =index($b$2:$m$2, mod(rows(b$14:b14) - 1, columns($b$2:$m$2) ) + 1)
c14: =index($a$2:$m$11, match(a14, $a$2:$a$11, 0), match(b14, $a$2:$m$2, 0) )
Last edited by shg; 08-27-2009 at 06:35 PM.
It looks like you are getting close, but the formula for A14 only results in 9 of each SKU, when there should be 12 since there are 12 potential sizes for each SKU. I have never used arrays before so I don't understand how they work.
Formula for B14 corrected in prior post.
Now you've got it! Thanks so much for your help! Looks like I need to read up on arrays, so I can understand how these formulas work!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks