Lookup function Question HELP

[Jennifer Waterhouse]

I'm trying to perform a lookup function

=LOOKUP(H8,sheet2!A:A,sheet2!C:C)

This formula only returns the first item of a list of items matching the
number in A:A (there could be more than 10 under the catalogue number in
A:A. I've made a list in B:B (1,2,3 etc.) for each of the catalogue numbers
in A:A

How would I augment B:B into the formula to coincide with the number in the
"B" column? (Be it 1, 2, 3 etc.)

EX.

A B C
2214 1 Red
2214 2 Green
2214 3 Brown

When I enter (2214) into H8 and I want the third item 'BROWN' to show in my
cell, what would be the formula?
Also, if there is no "3" then the cell should be left blank.

Thank you in advance

 

[Earl Kiosterud]

Jennifer,

LOOKUP looks up the first record that matches. It doesn't care about multiples. You can:

1) Filter the table (Data - Filter - Autofilter or Advanced Filter), looking for the records you want. This is a manual operation.

2) Have a macro do the above, copying the records wherever you want them.

To create the table you specify, a macro would have to be written.


It sounds as if you might should oughtta be using Access. Maybe.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I'm trying to perform a lookup function

=LOOKUP(H8,sheet2!A:A,sheet2!C:C)

This formula only returns the first item of a list of items matching the
number in A:A (there could be more than 10 under the catalogue number in
A:A. I've made a list in B:B (1,2,3 etc.) for each of the catalogue numbers
in A:A

How would I augment B:B into the formula to coincide with the number in the
"B" column? (Be it 1, 2, 3 etc.)

EX.

A B C
2214 1 Red
2214 2 Green
2214 3 Brown

When I enter (2214) into H8 and I want the third item 'BROWN' to show in my
cell, what would be the formula?
Also, if there is no "3" then the cell should be left blank.

Thank you in advance

 

[Max]

One way ..

Assuming this table is
in Sheet1, cols A to C
data from row1 down

A B C
2214 1 Red
2214 2 Green
2214 3 Brown

In Sheet2
-------------
You have listed the "look-up" criteria in cols A and B
and want to extract the result in col C
(data from row1 down)

A B C
2214 3
2214 1
etc

Put in C1:

=IF(ISNA(MATCH(1,(Sheet1!$A$1:$A$100=A1)*(Sheet1!$B$1:$B$100=B1),0)),"",INDE
X(Sheet1!$C$1:$C$100,MATCH(1,(Sheet1!$A$1:$A$100=A1)*(Sheet1!$B$1:$B$100=B1)
,0)))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

Copy C1 down

For the sample data: C1, C2 will return Brown, Red
Unmatched cases will return blanks ""

Adapt the ranges to suit

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
I'm trying to perform a lookup function

=LOOKUP(H8,sheet2!A:A,sheet2!C:C)

This formula only returns the first item of a list of items matching the
number in A:A (there could be more than 10 under the catalogue number in
A:A. I've made a list in B:B (1,2,3 etc.) for each of the catalogue numbers
in A:A

How would I augment B:B into the formula to coincide with the number in the
"B" column? (Be it 1, 2, 3 etc.)

EX.



When I enter (2214) into H8 and I want the third item 'BROWN' to show in my
cell, what would be the formula?
Also, if there is no "3" then the cell should be left blank.

Thank you in advance

 

[Dave Peterson]

So you have 2214 in H8.
Where do you have the 3? I'm gonna use G8.

Then try this:

=INDEX(Sheet2!C1:C999,MATCH(1,((Sheet2!A1:A999=H8)*(Sheet2!B1:B999=G8)),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But it'll return #n/a if there isn't a match.

So you could either hide the with conditional formatting--white font on white
fill (but the error is still there).

Or you could use something like:

=if(iserror(longformulahere),"",longformulahere)

Or just check the portion which causes the error:
=IF(ISERROR(MATCH(1,((Sheet2!A1:A999=H8)*(Sheet2!B1:B999=G8)),0)),"",
INDEX(Sheet2!C1:C999,MATCH(1,((Sheet2!A1:A999=H8)*(Sheet2!B1:B999=G8)),0)))

(all one cell--and still ctrl-shift-entered.)

 

[Dave Peterson]

ps. If you really want to post to lots of groups. Send one message to all the
groups at the same time (include them in the header).

That way, all the responses will go to each group, too.

But it's not usually necessary. This one fit nicely in the .worksheet.functions
newsgroup--even though you got multiple responses here.

 

[Nadeem Shafiullah]

Dear Jennifer
I would try to make new column merging column A and B. That way i would have a unique no. E.g when u join column A and B u will get 22141. So when u will enter 22143 it will get u the brown. There is function available in excel to join two columns

thanks
Nadeem
I'm trying to perform a lookup function

=LOOKUP(H8,sheet2!A:A,sheet2!C:C)

This formula only returns the first item of a list of items matching the
number in A:A (there could be more than 10 under the catalogue number in
A:A. I've made a list in B:B (1,2,3 etc.) for each of the catalogue numbers
in A:A

How would I augment B:B into the formula to coincide with the number in the
"B" column? (Be it 1, 2, 3 etc.)

EX.

A B C
2214 1 Red
2214 2 Green
2214 3 Brown

When I enter (2214) into H8 and I want the third item 'BROWN' to show in my
cell, what would be the formula?
Also, if there is no "3" then the cell should be left blank.

Thank you in advance