Home |
Search |
Today's Posts |
#1
|
|||
|
|||
LOOKUP VALUES
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 |
#2
|
|||
|
|||
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "gordo" wrote in message ups.com... 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 |
#3
|
|||
|
|||
On 26 Jun 2005 01:45:49 -0700, "gordo" wrote:
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 |
#4
|
|||
|
|||
Sorry i cant get the formula to work.Pls Help
I get #value error message |
#5
|
|||
|
|||
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? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "gordo" wrote in message ups.com... Sorry i cant get the formula to work.Pls Help I get #value error message |
#6
|
|||
|
|||
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. -- Meldoy ------------------------------------------------------------------------ Meldoy's Profile: http://www.excelforum.com/member.php...fo&userid=5449 View this thread: http://www.excelforum.com/showthread...hreadid=382290 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning all values from a lookup - not just the first/last one | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Lookup Function - Specific Values | Excel Worksheet Functions | |||
Multiple lookup value's | Excel Worksheet Functions | |||
Need help with lookup and comparing values | Excel Worksheet Functions |