Lookup in several tables

**afriedman** · 02-15-2011, 03:10 PM

I am trying to check my work and need to reference something that looks like this:
(see excel doc row 17-24. This is what I am trying to populate)

Basically, what I am trying to do here is the following:

Starting at C18 For all the mini tables I have there,

If it has that keyword
And if it is in the Revenue Column
And if it has the month of Aug

Then return the value, which in this case should be 265 (cell C8), and 214 (cell G8) etc...

But I cant get the formula working properly. Anyone have any ideas here?

**NBVC** · 02-15-2011, 03:20 PM

Try:

=INDEX($B$6:$AB$13,MATCH($C$16,$B$6:$B$13,0),MATCH($B18,$B$5:$AB$5,0)+1)

where C16 contains the search month: "Aug" (without quotes).

If you want the corresponding No of Orders, then:

=INDEX($B$6:$AB$13,MATCH($C$16,$B$6:$B$13,0),MATCH($B18,$B$5:$AB$5,0)+2)

**scottylad2** · 02-15-2011, 03:37 PM

By naming your tables and slightly altering what they are referencing this will work using indirect and indext/match

**scottylad2** · 02-15-2011, 04:21 PM

I've added a little CF and a Drop Down for your reference, and adjusted in case you also want to pull in the Number of Orders as well

**afriedman** · 02-15-2011, 05:57 PM

Thanks both of you! I actually ended up using NBVC's solution because I got it first, but to be honest scottylad2, I have no idea how your is working

... which is awesome! Can you please explain it more. I would be interested in learning about indirects!

Thanks again!

**scottylad2** · 02-15-2011, 07:45 PM

By naming your tables keyword1 keyword2 etc, then using the indirect you can reference a cell containing the same text string as your table name and it will convert it to an array of values, in short indirectly referencing whatever table name is in the cell.

While it works perfectly well with your current set up, it's even better say for instance if all your tables were on different tabs. by naming them and referring by indirect, you can avoid having to use sheet references.