Lookup in an Array

S

Steve

Can somebody tell me how to look up a list of items in a column from
an array and then return the information in a cell that is in a column
which is the same row that the found item occurs?....see below

FIND CUST SPEC TC1 TC2 TC3 TC4 TC5 TC6
row 1 CU227 AAA 24409 CU281 CU282 CU283 CU284 CU285 CU286
row 2 CU024 DDD 24444 CA952
row 3 CU284 WWW 24435 CU024
row 4 CA977 RRR 24467 CU281 CU282 CU283 CU284 CU285 CU286
row 5 CU242 VVV 24488 CA952 CA954 CA977
row 6 CU248 YYY 24463 CU210
row 7 CU254 OOO 24498 CU227 CU289
row 8 CU241 MMM 24434 CU241 CU242 CU244 CU248
row 9 CA952 PPP 24427 CU254

For example: If I do a find on the item "CU248" (listed in row 6
under the "FIND" column) from the information listed under the columns
labeled "TC1" through "TC6", I need to be able to return the "CUST"
and the "SPEC" that appear in the same row where "CU248" is found. In
this case the find on "CU248" should result in "MMM" for the "CUST"
and "24434" for the "SPEC".


Please help,

Steve
 
M

Max

One way:

Assuming the data range given is in A2:I10

Name this range as say: MyTable

In a new sheet, assume you have in col A,
A2 downwards, the items CU248, etc put in:

B2: =VLOOKUP(TRIM($A2),MyTable,COLUMN(),FALSE)

Copy across B2:C2,
then down as many rows as there items in col A

cols B & C will return the CUST & SPEC from MyTable
for the items in col A
 
L

Leo Heuser

Steve

What happens, when the item to find exists in
more than one row? (e.g. CU284)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
S

Steve

Leo,

Preferably...I would like it to give me what is in the "CUST" and the
"SPEC" that appear in the same row where the item is found for each
instance. However, if that is not possible...I'll take the first
instance that it shows up in the table. Sorry for not making that
distinction in my original posting.


Thanks,

Steve
 
A

Alan Beban

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, and
assuming your data is in A1:J10:

=OFFSET(INDIRECT(ArrayMatch("CU284",$E$2:$J$10,"A",4)),0,-(ColumnVector(ArrayMatch("CU284",$E$2:$J$10),2))-1-(1-COLUMN(A1)))

array entered into a column long enough to accommodate the output (in
this case 9 rows), and filled right one column, will return the list of
customers and specs and #N/A in the extra cells.

Alan Beban
 
L

Leo Heuser

Steve

Not exactly a beauty but the following formulae
seems to do the job.

Assuming headings (Cust, Spec, TC1 through 6) in B1:i1
and data in B2:i50.
Heading "Cust" in L2 and heading "Spec" in M2.
L1 is used for entering the item to look up.
I have also assumed, that all data in B2:B50 (Cust) is distinct.

In L3 enter this arrayformula:

=INDEX($B$2:$B$50,SMALL((COUNTIF(OFFSET($D$2:$I$2,
ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*(ROW($A$2:$A$50)-
ROW($A$2)+1),ROW()-ROW($L$3)+1+FREQUENCY((COUNTIF(
OFFSET($D$2:$I$2,ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*
(ROW($A$2:$A$50)-ROW($A$2)+1),0)))

The formula must be entered with <Shift><Ctrl><Enter> also if
edited later. If done correctly, Excel will display the formula in
the formula bar enclosed in curly brackets { } Don't enter these
brackets yourself.

In M3 enter this formula:

=INDEX($B$2:$C$50,MATCH(L3,$B$2:$B$50,0),2)

Select L3:M3 and copy them down with the fill handle (the little
square in the lower right corner of the selection) as far as
needed (the maximum number of times a distinct item can exist
in the TC1 - TC6 range).
Cells not returning an answer will contain the #NUM! error.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
A

Alan Beban

Leo said:
I have also assumed, that all data in B2:B50 (Cust) is distinct.
Click to expand...

For what it's worth, the formula I previously posted, which depends on
the downloadable add-in, does not have this restriction. I wonder if
Leo's formula can be modified to eliminate it.

Alan Beban
 
H

Harlan Grove

For what it's worth, the formula I previously posted, which depends on
the downloadable add-in, does not have this restriction. I wonder if
Leo's formula can be modified to eliminate it.
Click to expand...
...

Either modify Leo's formula or just mention that it has a restriction that your
formula doesn't. Spare us rhetorical questions!

As it happens, it's possible to use a different approach involving only built-in
functions. If the OP's data were in A1:I10 ('FIND' in A1), the customer code
sought in either a range or string expression named CC, and the topmost matching
CUST and SPEC col results returned in cells N1 and O1, respectively, enter the
followin array formula in N1:O1.

=INDEX($B$2:$C$10,SMALL((MMULT(--($D$2:$I$10=CC),
TRANSPOSE(COLUMN($D$2:$I$10)))>0)*(ROW($D$2:$I$10)-CELL("Row",$D$2:$I$10)+1)
+(MMULT(--($D$2:$I$10=CC),TRANSPOSE(COLUMN($D$2:$I$10)))=0)*65537,
ROW()-ROW($N$1)+1),{1,2})

Select N1:O1 and fill down until it gives #REF! errors. Multiple instances of CC
in the same row (so in different TC# columns) are treated as a single match, but
instances of CC in different rows (whatever TC# column(s)) are treated as
different multiple matches.
 
L

Leo Heuser

To eliminate the restriction:

In L3 arrayenter (<Shift><Ctrl><Enter>)

=INDEX(B$2:B$50,SMALL((COUNTIF(OFFSET($D$2:$I$2,
ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*(ROW($A$2:$A$50)-
ROW($A$2)+1),ROW()-ROW($L$3)+1+FREQUENCY((COUNTIF(
OFFSET($D$2:$I$2,ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*
(ROW($A$2:$A$50)-ROW($A$2)+1),0)))

Copy L3 to M3 with the fill handle.
Copy L3:M3 down with the fill handle.

The above formula is the one, I provided for my first answer, the
only difference being, that B is relative in B$2:B$50

Leo Heuser
 
A

Alan Beban

Thanks, Leo. I'm glad to see that you, at least, recognized that it
wasn't a rhetorical question.

I try to keep a mini-library of built-in formulas that obviate the need
for the array functions add-in; this is a helpful addition.

Alan Beban
 
S

Steve

Sorry, for not being able to get to the internet yesterday after I
left work to clear up any of the questions that were popping up on my
posting. Many thanks to everyone for the generous reccomendations. I
will try some of those out today...

I'm a bit of novice when it comes to anything more than a simple
"If..." formula. Let me try once more to make sure everyone knows
exactly what I was after...here goes

Can somebody tell me how to look up a list of items in a column from
an array and then return the information in a cell that is in a column
which is the same row that the found item occurs?....see below

FROM:

FIND CUST SPEC TC1 TC2 TC3 TC4 TC5 TC6
row 1 CU227 AAA 24409 CU281 CU282 CU283 CU284 CU285 CU286
row 2 CU024 VVV 24444 CA952
row 3 CU284 WWW 24435 CU024
row 4 CA977 AAA 24467 CU281 CU282 CU283 CU284 CU285 CU286
row 5 CU242 VVV 24488 CA952 CA954 CA977
row 6 CU248 YYY 24463 CU210
row 7 CU254 OOO 24498 CU227 CU289
row 8 CU241 MMM 24434 CU241 CU242 CU244 CU248
row 9 CA952 PPP 24427 CU254

For example: If I do a find on the item "CU284" (listed in row 3
under the "FIND" column) from the information listed under the columns
labeled "TC1" through "TC6", I need to be able to return the "CUST"
and the "SPEC" that appear in the same row where "CU284" is found for
all instances. In this case the find on "CU284" should result in
"AAA" for the "CUST" and "24409" and "24467" for the "SPEC".

Note: Items listed under the "CUST" column are not unique, where as
those listed under the "SPEC" column will be unigue.

The way I would like to see the result for this table to turn out
would be something like as follows, (perhaps on a separate worksheet):


FIND CUST SPEC
row 1 CA952 VVV 24444
row 2 CA952 VVV 24488
row 3 CA954 VVV 24488
row 4 CA977 VVV 24488
row 5 CU024 WWW 24435
row 6 CU210 YYY 24463
row 7 CU227 OOO 24498
row 8 CU241 MMM 24434
row 9 CU242 MMM 24434
row 10 CU244 MMM 24434
row 11 CU248 MMM 24434
row 12 CU254 PPP 24427
row 13 CU281 AAA 24409
row 14 CU281 AAA 24467
row 15 CU282 AAA 24409
row 16 CU282 AAA 24467
row 17 CU283 AAA 24409
row 18 CU283 AAA 24467
" " " "

I tried providing only a small sample of what I have. In reality, I
have roughly 1,250 rows of data like what is shown in the original
format above with approximately 30 columns of TC's. I was hoping
there might be some sort of macro that could do this for me. I was
able to do something with a suggestion someone sent from a similar
problem I had using the "INDIRECT" function and 55,000 rows. Really
slow...


Kind Regards,

Steve
 
A

Alan Beban

If your CU numbers in Columns TC1 to TC6 do not include more than one
instance of a CU number in any row, either Leo Heuser's second formula,
Harlan Grove's formula or mine, using the functions from the
downloadable file, will work for you. If there is more than one such
instance per row, Harlan Grove's formula will work, ignoring each second
(third, fourth, etc.) occurrence, i.e., returning CUST and SPEC from
that row once; my formula will return each such occurrence separately;
and Leo Heuser's formula will throw up, returning quirky results.

Alan Beban
 
S

Steve

Thanks again for all of your help... I'll try the one you've
reccomended this evening...

Kind Regards,

Steve
 
S

Steve

Harlan,

I'm trying to use your built-in solution, however I must be doing
something wrong. I did everything you had written and ended with the
Control+Shift+Enter and still only came up with the "#NAME?" error.
Any ideas where I might be doing something wrong? Please advise...


Regards,

Steve
 
H

Harlan Grove

I'm trying to use your built-in solution, however I must be doing
something wrong. I did everything you had written and ended with the
Control+Shift+Enter and still only came up with the "#NAME?" error.
Any ideas where I might be doing something wrong? Please advise... ...
Click to expand...

Did you name the customer code you're searching for CC? If not, and you're
entering it in, say, cell X99, replace the CC instances in the formula above
with $X$99. Failure to define the name CC or #NAME? errors in the data range are
the only ways my formula would return #NAME?.
 
S

Steve

Alan,

Me again... I tried to use your downloadable array functions, however
I'm not quite clear on how to use them or which one I should be using.
Do I paste this function in one of the columns and copy it down?
When in ask for a range for the array what group of cells do I select.
The formula only gives me the "#NAME? error when I paste it in my
worksheet, even after I do the Control+Shift+Enter thing....still
learning...Thanks again for your assistance.


Regards,

Steve
 
L

Leo Heuser

Just for the record. This version will work with duplicates
in all ranges (CUST, SPEC and TC1-TC6), returning CUST
and SPEC for a row only once in case of duplicates in that row:


=INDEX(B$2:B$50,SMALL(ROUNDUP(COUNTIF(OFFSET($D$2:$I$2,
ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1)/COLUMNS($D$2:$I$2),0)*
(ROW($A$2:$A$50)-ROW($A$2)+1),ROW()-ROW($L$3)+1+
FREQUENCY((COUNTIF(OFFSET($D$2:$I$2,ROW($A$2:$A$50)-
ROW($A$2),0,1),$L$1))*(ROW($A$2:$A$50)-ROW($A$2)+1),0)))

Still to be array-entered.

Leo Heuser
 
S

Steve

Leo,

So far yours was the only one I've managed to make work. Not that I'm
doubting the work that the others have done, it's just me being a
novice at Excel and having to muddle my way through things. However,
if you look at the posting I made back to Alan which was the 13th in
the string, I tried to better explain what I was after. Basically, I
needed each unique entity listed a row be re-routed in a column with
its corresponding "CUST" and "SPEC" beside it, i.e. transposing all of
the data into one single column. Any ideas on how to do this with
your formula would be greatly appreciated.

Thanks,

Steve
 
A

Alan Beban

When you download the file you need to save it as an add-in; then in
your relevant workbook, in the VBE Editor, select Tools|References and
check that item in the list of libraries avaliable.

Alan Beban
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transform data from various cells into a single column 2

Top