| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two possible values with a lookup?
Here is what I have:
A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
If there are *only the 2 possibilities* you can use the lookup for the 1st
instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
Hi,
In one cell use =VLOOKUP(A9,A1:B4,2,) Then the following will return the second one even if they are both X or both R. =VLOOKUP(A10,INDIRECT("A"&MATCH(A10,A1:A4,0)+1&":B 4"),2,0) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Homer" wrote: Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
Thanks for the help.
What would you do if there were three or more possibilities? Would you just string if statements along or is there a better way? "T. Valko" wrote: If there are *only the 2 possibilities* you can use the lookup for the 1st instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
You would need a more complicated formula. How would you want the results
displayed: 98765...result1...result2...result3 Or 98765...result1 ..............result2 ..............result3 -- Biff Microsoft Excel MVP "Homer" wrote in message ... Thanks for the help. What would you do if there were three or more possibilities? Would you just string if statements along or is there a better way? "T. Valko" wrote: If there are *only the 2 possibilities* you can use the lookup for the 1st instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
I was trying to cover a few different scenarios with one question. Your
original answer took care of most. For the complicated one I will use hlookup. Here is an example of the complicated one: 74019 74019 39008 39008 69861 74831 74831 74831 row of cells with data for other uses row of cells with data for other uses 3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4" empty cell 1,000 5,000 600 800 3,000 50 300 0 In another area of the same sheet I combine information of all like numbers from the top row using sum if. The result would be: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. I would like to use hlookup, but it will only find the first number and does not show if there is a second. I hope you can understand my ramblings. "T. Valko" wrote: You would need a more complicated formula. How would you want the results displayed: 98765...result1...result2...result3 Or 98765...result1 ..............result2 ..............result3 -- Biff Microsoft Excel MVP "Homer" wrote in message ... Thanks for the help. What would you do if there were three or more possibilities? Would you just string if statements along or is there a better way? "T. Valko" wrote: If there are *only the 2 possibilities* you can use the lookup for the 1st instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
74019 39008 69861 74831
6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. Not sure I follow you. Do you mean instead of 0 you want a blank cell? -- Biff Microsoft Excel MVP "Homer" wrote in message ... I was trying to cover a few different scenarios with one question. Your original answer took care of most. For the complicated one I will use hlookup. Here is an example of the complicated one: 74019 74019 39008 39008 69861 74831 74831 74831 row of cells with data for other uses row of cells with data for other uses 3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4" empty cell 1,000 5,000 600 800 3,000 50 300 0 In another area of the same sheet I combine information of all like numbers from the top row using sum if. The result would be: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. I would like to use hlookup, but it will only find the first number and does not show if there is a second. I hope you can understand my ramblings. "T. Valko" wrote: You would need a more complicated formula. How would you want the results displayed: 98765...result1...result2...result3 Or 98765...result1 ..............result2 ..............result3 -- Biff Microsoft Excel MVP "Homer" wrote in message ... Thanks for the help. What would you do if there were three or more possibilities? Would you just string if statements along or is there a better way? "T. Valko" wrote: If there are *only the 2 possibilities* you can use the lookup for the 1st instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
A zero would be best.
Don "T. Valko" wrote: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. Not sure I follow you. Do you mean instead of 0 you want a blank cell? -- Biff Microsoft Excel MVP "Homer" wrote in message ... I was trying to cover a few different scenarios with one question. Your original answer took care of most. For the complicated one I will use hlookup. Here is an example of the complicated one: 74019 74019 39008 39008 69861 74831 74831 74831 row of cells with data for other uses row of cells with data for other uses 3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4" empty cell 1,000 5,000 600 800 3,000 50 300 0 In another area of the same sheet I combine information of all like numbers from the top row using sum if. The result would be: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. I would like to use hlookup, but it will only find the first number and does not show if there is a second. I hope you can understand my ramblings. "T. Valko" wrote: You would need a more complicated formula. How would you want the results displayed: 98765...result1...result2...result3 Or 98765...result1 ..............result2 ..............result3 -- Biff Microsoft Excel MVP "Homer" wrote in message ... Thanks for the help. What would you do if there were three or more possibilities? Would you just string if statements along or is there a better way? "T. Valko" wrote: If there are *only the 2 possibilities* you can use the lookup for the 1st instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
Try this:
This data in the range A1:H1 74019,74019,39008,39008,69861,74831,74831,74831 This data in the range A4:H4 3/4",3/4",1/2",3/4",1/2",1/2",3/4" This data in the range A5:H5 1000,5000,600,800,3000,50,300,0 These headers in the range B10:E10 74019,39008,69861,74831 A11 = 3/4" A12 = 1/2" Enter this formula in B11: =SUMPRODUCT(--($A$1:$H$1=B$10),--($A$4:$H$4=$A11),$A$5:$H$5) Copy across to E11 then down to B12:E12. -- Biff Microsoft Excel MVP "Homer" wrote in message ... A zero would be best. Don "T. Valko" wrote: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. Not sure I follow you. Do you mean instead of 0 you want a blank cell? -- Biff Microsoft Excel MVP "Homer" wrote in message ... I was trying to cover a few different scenarios with one question. Your original answer took care of most. For the complicated one I will use hlookup. Here is an example of the complicated one: 74019 74019 39008 39008 69861 74831 74831 74831 row of cells with data for other uses row of cells with data for other uses 3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4" empty cell 1,000 5,000 600 800 3,000 50 300 0 In another area of the same sheet I combine information of all like numbers from the top row using sum if. The result would be: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. I would like to use hlookup, but it will only find the first number and does not show if there is a second. I hope you can understand my ramblings. "T. Valko" wrote: You would need a more complicated formula. How would you want the results displayed: 98765...result1...result2...result3 Or 98765...result1 ..............result2 ..............result3 -- Biff Microsoft Excel MVP "Homer" wrote in message ... Thanks for the help. What would you do if there were three or more possibilities? Would you just string if statements along or is there a better way? "T. Valko" wrote: If there are *only the 2 possibilities* you can use the lookup for the 1st instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
I'm not sure if you are still monitoring this thread. If you are, thank you
very much. Your formula works as needed. "T. Valko" wrote: Try this: This data in the range A1:H1 74019,74019,39008,39008,69861,74831,74831,74831 This data in the range A4:H4 3/4",3/4",1/2",3/4",1/2",1/2",3/4" This data in the range A5:H5 1000,5000,600,800,3000,50,300,0 These headers in the range B10:E10 74019,39008,69861,74831 A11 = 3/4" A12 = 1/2" Enter this formula in B11: =SUMPRODUCT(--($A$1:$H$1=B$10),--($A$4:$H$4=$A11),$A$5:$H$5) Copy across to E11 then down to B12:E12. -- Biff Microsoft Excel MVP "Homer" wrote in message ... A zero would be best. Don "T. Valko" wrote: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. Not sure I follow you. Do you mean instead of 0 you want a blank cell? -- Biff Microsoft Excel MVP "Homer" wrote in message ... I was trying to cover a few different scenarios with one question. Your original answer took care of most. For the complicated one I will use hlookup. Here is an example of the complicated one: 74019 74019 39008 39008 69861 74831 74831 74831 row of cells with data for other uses row of cells with data for other uses 3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4" empty cell 1,000 5,000 600 800 3,000 50 300 0 In another area of the same sheet I combine information of all like numbers from the top row using sum if. The result would be: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. I would like to use hlookup, but it will only find the first number and does not show if there is a second. I hope you can understand my ramblings. "T. Valko" wrote: You would need a more complicated formula. How would you want the results displayed: 98765...result1...result2...result3 Or 98765...result1 ..............result2 ..............result3 -- Biff Microsoft Excel MVP "Homer" wrote in message ... Thanks for the help. What would you do if there were three or more possibilities? Would you just string if statements along or is there a better way? "T. Valko" wrote: If there are *only the 2 possibilities* you can use the lookup for the 1st instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Two possible values with a lookup?
I'm not sure if you are still monitoring this thread.
I watch threads I've replied to for about 10 days. You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Homer" wrote in message ... I'm not sure if you are still monitoring this thread. If you are, thank you very much. Your formula works as needed. "T. Valko" wrote: Try this: This data in the range A1:H1 74019,74019,39008,39008,69861,74831,74831,74831 This data in the range A4:H4 3/4",3/4",1/2",3/4",1/2",1/2",3/4" This data in the range A5:H5 1000,5000,600,800,3000,50,300,0 These headers in the range B10:E10 74019,39008,69861,74831 A11 = 3/4" A12 = 1/2" Enter this formula in B11: =SUMPRODUCT(--($A$1:$H$1=B$10),--($A$4:$H$4=$A11),$A$5:$H$5) Copy across to E11 then down to B12:E12. -- Biff Microsoft Excel MVP "Homer" wrote in message ... A zero would be best. Don "T. Valko" wrote: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. Not sure I follow you. Do you mean instead of 0 you want a blank cell? -- Biff Microsoft Excel MVP "Homer" wrote in message ... I was trying to cover a few different scenarios with one question. Your original answer took care of most. For the complicated one I will use hlookup. Here is an example of the complicated one: 74019 74019 39008 39008 69861 74831 74831 74831 row of cells with data for other uses row of cells with data for other uses 3/4" 3/4" 1/2" 3/4" 1/2" 1/2" 3/4" empty cell 1,000 5,000 600 800 3,000 50 300 0 In another area of the same sheet I combine information of all like numbers from the top row using sum if. The result would be: 74019 39008 69861 74831 6,000 800 0 300 this line for 3/4" 0 600 3,000 50 this line for 1/2" If there is no 3/4" or 1/2", I do not want anything done. I would like to use hlookup, but it will only find the first number and does not show if there is a second. I hope you can understand my ramblings. "T. Valko" wrote: You would need a more complicated formula. How would you want the results displayed: 98765...result1...result2...result3 Or 98765...result1 ..............result2 ..............result3 -- Biff Microsoft Excel MVP "Homer" wrote in message ... Thanks for the help. What would you do if there were three or more possibilities? Would you just string if statements along or is there a better way? "T. Valko" wrote: If there are *only the 2 possibilities* you can use the lookup for the 1st instance and then something like this for the 2nd: X1 formula: =VLOOKUP(98765,A2:D5,2,0) Y1 formula: =IF(COUNTIF(A2:A5,98765)=2,IF(X1="R","X","R"),"") -- Biff Microsoft Excel MVP "Homer" wrote in message ... Here is what I have: A B C D 12345 X 9 a 98765 R 3 t 98765 X 4 b 56489 X 7 p Column B has two options X or R. Through validation and list I have the ability to choose either X or R in Column B. Some times Column A has multiple combinations of information. In another area of the sheet I need to have both the X and R show up. If I use vlookup the first instance only, is shown. What can I do? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| i wish to lookup values in column A, & add adjacent values in colu | Excel Discussion (Misc queries) | |||
| Lookup with two lookup values | Excel Discussion (Misc queries) | |||
| How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
| Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
| How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Linear Mode
