Lookup Data based on 2 or more conditions/arguments

[Guest]

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!

 

[T. Valko]

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

 

[Guest]

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]

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