Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Issue
I have a problem that I require help on.
I am working on a big spreadsheet. I have to pull information from one sheet into another. However I have 2 lookups to do. Is this possible. example - Info Sheet A B C 1 Manchester ABC Total -230.30 2 Manchester DEF Total +123.34 Example - Working Sheet A B C 1 Store Name ABC Total DEF Total 2 Manchester I need to lookup "Manchester" and "ABC Total" to give me "-230.30" in a cell. Any Ideas if this is possible, or another solution!! Thanks in advance Jayz |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Issue
I find it easier with something like this to insert a new column C in
the Info sheet and to concatenate column A and B together to give a unique reference, i.e.: =A1&B1 copied down. Now you can use columns C and D as your lookup table. A typical formula for the layout you show would be something like this in B2 of the Working sheet: =VLOOKUP($A2&B$1,Info!$C:$D,2,0) Copy into C2, then down if required. Hope this helps. Pete On May 8, 1:16*am, Jayz wrote: I have a problem that I require help on. I am working on a big spreadsheet. I have to pull information from one sheet into another. However I have 2 lookups to do. Is this possible. example - Info Sheet A B C 1 Manchester ABC Total -230.30 2 Manchester DEF Total +123.34 Example - Working Sheet A B C 1 Store Name ABC Total DEF Total 2 Manchester I need to lookup "Manchester" and "ABC Total" to give me "-230.30" in a cell. Any Ideas if this is possible, or another solution!! Thanks in advance Jayz |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Issue
You could join the two columns together in a third column and do the look up in that column... =B1 & C1 would give you "ManchesterABC Total" to lookup. -- Jim Cone Portland, Oregon USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jayz" wrote in message I have a problem that I require help on. I am working on a big spreadsheet. I have to pull information from one sheet into another. However I have 2 lookups to do. Is this possible. example - Info Sheet A B C 1 Manchester ABC Total -230.30 2 Manchester DEF Total +123.34 Example - Working Sheet A B C 1 Store Name ABC Total DEF Total 2 Manchester I need to lookup "Manchester" and "ABC Total" to give me "-230.30" in a cell. Any Ideas if this is possible, or another solution!! Thanks in advance Jayz |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Issue
Thanks for your prompt responce.
Unfortunately, the suggestion doesn't help me as the spread sheet is also feeding other information utilitising the "Branch" names. I wish I'd never started this project!! Ha Ha Regards Jayz "Pete_UK" wrote: I find it easier with something like this to insert a new column C in the Info sheet and to concatenate column A and B together to give a unique reference, i.e.: =A1&B1 copied down. Now you can use columns C and D as your lookup table. A typical formula for the layout you show would be something like this in B2 of the Working sheet: =VLOOKUP($A2&B$1,Info!$C:$D,2,0) Copy into C2, then down if required. Hope this helps. Pete On May 8, 1:16 am, Jayz wrote: I have a problem that I require help on. I am working on a big spreadsheet. I have to pull information from one sheet into another. However I have 2 lookups to do. Is this possible. example - Info Sheet A B C 1 Manchester ABC Total -230.30 2 Manchester DEF Total +123.34 Example - Working Sheet A B C 1 Store Name ABC Total DEF Total 2 Manchester I need to lookup "Manchester" and "ABC Total" to give me "-230.30" in a cell. Any Ideas if this is possible, or another solution!! Thanks in advance Jayz |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Issue
*Maybe* this...
Entered on the Working sheet in cell B2: =SUMPRODUCT(--(Info!$A$1:$A$5=$A2),--(Info!$B$1:$B$5=B$1),Info!$C$1:$C$5) Copy across then down as needed. -- Biff Microsoft Excel MVP "Jayz" wrote in message ... I have a problem that I require help on. I am working on a big spreadsheet. I have to pull information from one sheet into another. However I have 2 lookups to do. Is this possible. example - Info Sheet A B C 1 Manchester ABC Total -230.30 2 Manchester DEF Total +123.34 Example - Working Sheet A B C 1 Store Name ABC Total DEF Total 2 Manchester I need to lookup "Manchester" and "ABC Total" to give me "-230.30" in a cell. Any Ideas if this is possible, or another solution!! Thanks in advance Jayz |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Issue
You will still have the branch information in columns A and B, so you
can still do that. Pete On May 8, 1:52*am, Jayz wrote: Thanks for your prompt responce. Unfortunately, the suggestion doesn't help me as the spread sheet is also feeding other information utilitising the "Branch" names. I wish I'd never started this project!! Ha Ha Regards Jayz "Pete_UK" wrote: I find it easier with something like this to insert a new column C in the Info sheet and to concatenate column A and B together to give a unique reference, i.e.: =A1&B1 copied down. Now you can use columns C and D as your lookup table. A typical formula for the layout you show would be something like this in B2 of the Working sheet: =VLOOKUP($A2&B$1,Info!$C:$D,2,0) Copy into C2, then down if required. Hope this helps. Pete On May 8, 1:16 am, Jayz wrote: I have a problem that I require help on. I am working on a big spreadsheet. I have to pull information from one sheet into another. However I have 2 lookups to do. Is this possible. example - Info Sheet A B C 1 Manchester ABC Total -230.30 2 Manchester DEF Total +123.34 Example - Working Sheet A B C 1 Store Name ABC Total DEF Total 2 Manchester I need to lookup "Manchester" and "ABC Total" to give me "-230.30" in a cell. Any Ideas if this is possible, or another solution!! Thanks in advance Jayz- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
LOOKUP Command Issue | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) |