Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Data based on 2 or more conditions/arguments
Hi, I was wondering if someone could help me with a data retrieval/lookup
issue I am having. I am setting up a Workbook that tracks royalty reports for 3 dozen licensees we work with. I am outputting data from our accounting system to a worksheet and I want to look up the sales revenue for each licensee and copy/retrieve that data into the template. The problem is, there are multiple licensees with multiple product types. I have tried doing this with a nested vlookup like this: =VLOOKUP(A13,QBData_Feb!A:I,VLOOKUP(C4,QBData_Feb! B:I,8,FALSE),FALSE) I can see in the formula editor where the value I want is being identified next to the Col_index_num line, but this is not being dumped to the cell as my data. Perhaps someone can offer an alternative? My data looks like this: DataSheet: QBData_Feb DataRange: QBData_Feb!A:I Lookup/Template Sheet: Royalty Report (Feb07) Lookup Criteria1: various licensees in column A Lookup Criteria2: various product types in row 4 Basically, I need this to look up each licensee by product, see if they have any royalties due to them in the outputted datasheet, and then transfer that data into the corresponding cell on the template. Any thoughts are appreciated. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Data based on 2 or more conditions/arguments
Your second lookup has to return a number from 1 to 9 which corresponds to
the number of columns in QBData_Feb!A:I. Is that what it's doing? Biff "TravisB" wrote in message ... Hi, I was wondering if someone could help me with a data retrieval/lookup issue I am having. I am setting up a Workbook that tracks royalty reports for 3 dozen licensees we work with. I am outputting data from our accounting system to a worksheet and I want to look up the sales revenue for each licensee and copy/retrieve that data into the template. The problem is, there are multiple licensees with multiple product types. I have tried doing this with a nested vlookup like this: =VLOOKUP(A13,QBData_Feb!A:I,VLOOKUP(C4,QBData_Feb! B:I,8,FALSE),FALSE) I can see in the formula editor where the value I want is being identified next to the Col_index_num line, but this is not being dumped to the cell as my data. Perhaps someone can offer an alternative? My data looks like this: DataSheet: QBData_Feb DataRange: QBData_Feb!A:I Lookup/Template Sheet: Royalty Report (Feb07) Lookup Criteria1: various licensees in column A Lookup Criteria2: various product types in row 4 Basically, I need this to look up each licensee by product, see if they have any royalties due to them in the outputted datasheet, and then transfer that data into the corresponding cell on the template. Any thoughts are appreciated. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Data based on 2 or more conditions/arguments
Hey Biff, thanks for the input. I will claim some ignorance on this. The
second vlookup is looking in column 8 of the specified range, so yes, that is what it's doing and it is indeed finding the correct value/number. For whatever reason, this value isn't being carried through to the final result though, whether I indicate TRUE/FALSE or leave it blank in the final component of the function. This may not be the best way to do this (certainly since it's not working as hoped), so I'm open to doing this a different way if there are other suggestions. "T. Valko" wrote: Your second lookup has to return a number from 1 to 9 which corresponds to the number of columns in QBData_Feb!A:I. Is that what it's doing? Biff "TravisB" wrote in message ... Hi, I was wondering if someone could help me with a data retrieval/lookup issue I am having. I am setting up a Workbook that tracks royalty reports for 3 dozen licensees we work with. I am outputting data from our accounting system to a worksheet and I want to look up the sales revenue for each licensee and copy/retrieve that data into the template. The problem is, there are multiple licensees with multiple product types. I have tried doing this with a nested vlookup like this: =VLOOKUP(A13,QBData_Feb!A:I,VLOOKUP(C4,QBData_Feb! B:I,8,FALSE),FALSE) I can see in the formula editor where the value I want is being identified next to the Col_index_num line, but this is not being dumped to the cell as my data. Perhaps someone can offer an alternative? My data looks like this: DataSheet: QBData_Feb DataRange: QBData_Feb!A:I Lookup/Template Sheet: Royalty Report (Feb07) Lookup Criteria1: various licensees in column A Lookup Criteria2: various product types in row 4 Basically, I need this to look up each licensee by product, see if they have any royalties due to them in the outputted datasheet, and then transfer that data into the corresponding cell on the template. Any thoughts are appreciated. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Data based on 2 or more conditions/arguments
The second vlookup is looking in column 8 of the specified range,
so yes, that is what it's doing and it is indeed finding the correct value/number. It may be looking in column 8 but what value/number is in column 8? It has to be a number from 1 to 9. If it's any other value the formula won't work. The column_index_number tells Vlookup in which column to find the result. The column_index_number must be a number from 1 to the number of columns in the lookup table. So, if your first lookup table is in the range QBData_Feb!A:I, Then the result of the 2nd lookup function: VLOOKUP(C4,QBData_Feb!B:I,8,FALSE) *must* be a number from 1 to 9 which corresponds to the number of columns in QBData_Feb!A:I Biff "TravisB" wrote in message ... Hey Biff, thanks for the input. I will claim some ignorance on this. The second vlookup is looking in column 8 of the specified range, so yes, that is what it's doing and it is indeed finding the correct value/number. For whatever reason, this value isn't being carried through to the final result though, whether I indicate TRUE/FALSE or leave it blank in the final component of the function. This may not be the best way to do this (certainly since it's not working as hoped), so I'm open to doing this a different way if there are other suggestions. "T. Valko" wrote: Your second lookup has to return a number from 1 to 9 which corresponds to the number of columns in QBData_Feb!A:I. Is that what it's doing? Biff "TravisB" wrote in message ... Hi, I was wondering if someone could help me with a data retrieval/lookup issue I am having. I am setting up a Workbook that tracks royalty reports for 3 dozen licensees we work with. I am outputting data from our accounting system to a worksheet and I want to look up the sales revenue for each licensee and copy/retrieve that data into the template. The problem is, there are multiple licensees with multiple product types. I have tried doing this with a nested vlookup like this: =VLOOKUP(A13,QBData_Feb!A:I,VLOOKUP(C4,QBData_Feb! B:I,8,FALSE),FALSE) I can see in the formula editor where the value I want is being identified next to the Col_index_num line, but this is not being dumped to the cell as my data. Perhaps someone can offer an alternative? My data looks like this: DataSheet: QBData_Feb DataRange: QBData_Feb!A:I Lookup/Template Sheet: Royalty Report (Feb07) Lookup Criteria1: various licensees in column A Lookup Criteria2: various product types in row 4 Basically, I need this to look up each licensee by product, see if they have any royalties due to them in the outputted datasheet, and then transfer that data into the corresponding cell on the template. Any thoughts are appreciated. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup in data table - too many arguments? | Excel Worksheet Functions | |||
traverse data based on 2 conditions | Excel Discussion (Misc queries) | |||
lookup column label based on data in cell | Excel Worksheet Functions | |||
Lookup based on two conditions | Excel Worksheet Functions | |||
how do I lookup data based on two columns of data | Excel Worksheet Functions |