LOOKUP VALUES

[gordo]

Can someone please help
I have the following workbook with a selection of the data below:

INUMBR ILONGD WHHAND WHCOMM WHPEND WHSLOT01
175552 WHIRLPOOL AWM1404/4 1400RPM WASHER (AAB,5KG) 154 18 0 R4015
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2 0 R2023
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2 0 R4007
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2 0 R4018
247308 BOSCH WFO2467 1200RPM WM (CLASIXX,A+AB,6) 128 29 0 B1043
What i am trying to do is for example:
Inumber 247294 appears above 3 times with different locations , the
inumber being in A1-A1000 (Some Inumber appear more than once) and the
Locations in h1-h1000.
I have a seperate workbook with a list of Inumbr which is only
displayed once. What i want to do is bring back all the locations for
the Inumber.
For example : 247294 has three locations R2023,r4007,r4018. Using
vlookup only brings back the first location (r2023)and dosent allow the
second value and third respectively.
Is there any way of displaying these.
Note =cell is not an option (workbook changes)

Many thanks

Gordon

 

[Ragdyer]

If your unique inumber list is in Column A of Book2,
And your data list is in Book1, as you said, from A1 to H1000,
Then enter this *array* formula in Column B of Book2, and copy across the
columns, as far as you think there are that many locations per inumber.
Then, copy down as needed, to reference the entire list of unique inumbers
in Column A.

=INDEX([Book1]Sheet1!$H$1:$H$1000,SMALL(IF([Book1]Sheet1!$A$1:$A$1000=$A1,RO
W([Book1]Sheet1!$A$1:$A$1000),""),COLUMN(A:A)))
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

You'll see the #NUM! error when you run out of locations to return.

 

[Ron Rosenfeld]

Can someone please help
I have the following workbook with a selection of the data below:

INUMBR ILONGD WHHAND WHCOMM WHPEND WHSLOT01
175552 WHIRLPOOL AWM1404/4 1400RPM WASHER (AAB,5KG) 154 18 0 R4015
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2 0 R2023
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2 0 R4007
247294 BOSCH WFL2067 1000RPM WM(CLASIXX,A+AC,6) 111 2 0 R4018
247308 BOSCH WFO2467 1200RPM WM (CLASIXX,A+AB,6) 128 29 0 B1043
What i am trying to do is for example:
Inumber 247294 appears above 3 times with different locations , the
inumber being in A1-A1000 (Some Inumber appear more than once) and the
Locations in h1-h1000.
I have a seperate workbook with a list of Inumbr which is only
displayed once. What i want to do is bring back all the locations for
the Inumber.
For example : 247294 has three locations R2023,r4007,r4018. Using
vlookup only brings back the first location (r2023)and dosent allow the
second value and third respectively.
Is there any way of displaying these.
Note =cell is not an option (workbook changes)

Many thanks

Gordon

You could adapt one of the solutions provided you in your very similar request
which you posted yesterday.


--ron

 

[gordo]

Sorry i cant get the formula to work.Pls Help
I get #value error message

 

[Ragdyer]

Did you enter the formula using CSE?

This is an *array* formula!

Repeating what I posted with the formula:
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

You must *ALSO* use CSE, if and every time you revise the formula!

Click in a cell that contains a formula that is returning the #VALUE! error.
THEN, click in the formula bar.
THEN, CSE!

Does that help?

 

[Meldoy]

Sorry not trying to jack the thread, just thought I would ask one of my
millions of questions dealing with the lookup function here.

I have a sheet that I am using to help me on a daily basis. To keep
from typing the same text constantly on a daily basis I have started to
use a lookup formula. To keep from constantly copy + pasting the formula
I did a conditional format. It works great, but what I need to know is
if there is any way I can not have to type the lookup value in one
place, then go down and delete it in another?

ie.

Cell B4 has a 1 causing C4 to bring up the text Reports Completed under
the heading Jobs done

Then under the heading Jobs running I would need to delete the 1 from
cell 10B so that it would not be there.

I know how lazy, but it will give me more time to do other jobs and not
have to worry about forgetting to remove anything from the Jobs Running
section or the Jobs waiting section.
Thanks in advance to anyone.