| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup valid combination of multiple cells
Is it possible to lookup a valid match of 2 values in separate columns, and
provide the value of a related third column? Sheet1 Name Code Amount Ant 003 100 Bil 002 200 Claire 001 300 Sheet2 Full code Amount Ant003 The Full code in Sheet2 is entered manually and I wish to keep this. The Amount cell in Sheet2 should lookup the full code (eg Ant003) directly from the concatenation of the Name and Code fields in Sheet1 (in this case it should display 100) Is it possible to retrieve the amount automatically without having to create a separate lookup table, as this is the current method and I want to lose this intermediary step? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup valid combination of multiple cells
Try in the Amount field (B2) on Sheet2:
=SUMPRODUCT(--(Sheet1!$B$2:$B$4=RIGHT(A2,3)),--(Sheet1!$A$2:$A$4=LEFT(A2,LEN(A2)-3)),--(Sheet1!$C$2:$C$4)) This assumes Code is always 3 characters long. HTH "Gerard" wrote: Is it possible to lookup a valid match of 2 values in separate columns, and provide the value of a related third column? Sheet1 Name Code Amount Ant 003 100 Bil 002 200 Claire 001 300 Sheet2 Full code Amount Ant003 The Full code in Sheet2 is entered manually and I wish to keep this. The Amount cell in Sheet2 should lookup the full code (eg Ant003) directly from the concatenation of the Name and Code fields in Sheet1 (in this case it should display 100) Is it possible to retrieve the amount automatically without having to create a separate lookup table, as this is the current method and I want to lose this intermediary step? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup valid combination of multiple cells
That's worked! Thank you very much.
Just out of interest, if the code was either: - Always 2 characters long - Variable What impact would this have on the function? Once again, thanks for your help. Gerard "Toppers" wrote: Try in the Amount field (B2) on Sheet2: =SUMPRODUCT(--(Sheet1!$B$2:$B$4=RIGHT(A2,3)),--(Sheet1!$A$2:$A$4=LEFT(A2,LEN(A2)-3)),--(Sheet1!$C$2:$C$4)) This assumes Code is always 3 characters long. HTH "Gerard" wrote: Is it possible to lookup a valid match of 2 values in separate columns, and provide the value of a related third column? Sheet1 Name Code Amount Ant 003 100 Bil 002 200 Claire 001 300 Sheet2 Full code Amount Ant003 The Full code in Sheet2 is entered manually and I wish to keep this. The Amount cell in Sheet2 should lookup the full code (eg Ant003) directly from the concatenation of the Name and Code fields in Sheet1 (in this case it should display 100) Is it possible to retrieve the amount automatically without having to create a separate lookup table, as this is the current method and I want to lose this intermediary step? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup valid combination of multiple cells
If 2 characters long:
=SUMPRODUCT(--(Sheet1!$B$2:$B$4=RIGHT(A2,2)),--(Sheet1!$A$2:$A$4=LEFT(A2,LEN(A2)-2)),--(Sheet1!$C$2:$C$4)) If the length is variable, then we would have to test for a numeric string (the code) and determine its length so it's rather more complicated. "Gerard" wrote: That's worked! Thank you very much. Just out of interest, if the code was either: - Always 2 characters long - Variable What impact would this have on the function? Once again, thanks for your help. Gerard "Toppers" wrote: Try in the Amount field (B2) on Sheet2: =SUMPRODUCT(--(Sheet1!$B$2:$B$4=RIGHT(A2,3)),--(Sheet1!$A$2:$A$4=LEFT(A2,LEN(A2)-3)),--(Sheet1!$C$2:$C$4)) This assumes Code is always 3 characters long. HTH "Gerard" wrote: Is it possible to lookup a valid match of 2 values in separate columns, and provide the value of a related third column? Sheet1 Name Code Amount Ant 003 100 Bil 002 200 Claire 001 300 Sheet2 Full code Amount Ant003 The Full code in Sheet2 is entered manually and I wish to keep this. The Amount cell in Sheet2 should lookup the full code (eg Ant003) directly from the concatenation of the Name and Code fields in Sheet1 (in this case it should display 100) Is it possible to retrieve the amount automatically without having to create a separate lookup table, as this is the current method and I want to lose this intermediary step? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup valid combination of multiple cells
In sheet 2:
=SUMPRODUCT(--(Name&Code=A2),Amount) "Gerard" wrote: Is it possible to lookup a valid match of 2 values in separate columns, and provide the value of a related third column? Sheet1 Name Code Amount Ant 003 100 Bil 002 200 Claire 001 300 Sheet2 Full code Amount Ant003 The Full code in Sheet2 is entered manually and I wish to keep this. The Amount cell in Sheet2 should lookup the full code (eg Ant003) directly from the concatenation of the Name and Code fields in Sheet1 (in this case it should display 100) Is it possible to retrieve the amount automatically without having to create a separate lookup table, as this is the current method and I want to lose this intermediary step? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup valid combination of multiple cells
That works perfectly, thanks.
"Teethless mama" wrote: In sheet 2: =SUMPRODUCT(--(Name&Code=A2),Amount) "Gerard" wrote: Is it possible to lookup a valid match of 2 values in separate columns, and provide the value of a related third column? Sheet1 Name Code Amount Ant 003 100 Bil 002 200 Claire 001 300 Sheet2 Full code Amount Ant003 The Full code in Sheet2 is entered manually and I wish to keep this. The Amount cell in Sheet2 should lookup the full code (eg Ant003) directly from the concatenation of the Name and Code fields in Sheet1 (in this case it should display 100) Is it possible to retrieve the amount automatically without having to create a separate lookup table, as this is the current method and I want to lose this intermediary step? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| returning multiple cells for a single lookup | Excel Discussion (Misc queries) | |||
| counting valid dates in a range of cells | Excel Worksheet Functions | |||
| VLOOKUP - Multiple cells in lookup value | Excel Worksheet Functions | |||
| multiple combination bars chart | Charts and Charting in Excel | |||
| How do I Select Multiple entries from Valid list for a Cell | Excel Discussion (Misc queries) | |||
Linear Mode
