Multiple Criteria Lookup

[Nicole87]

Hi,

I am trying to write a formula that picks up a value within a set of data with two criterias, one is in a row and the other in a column - is this possible?

This is the table where I want to create the formulas. I am trying to pick up the value with the date and the type (eg. SIG).
[TABLE="width: 803"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 739"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD] 8[/TD]
[TD] 9[/TD]
[TD][/TD]
[TD] 10[/TD]
[TD] 11[/TD]
[TD] 12[/TD]
[TD] 13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]16/02/2014[/TD]
[TD] 23/02/2014[/TD]
[TD] 28/02/2014[/TD]
[TD][/TD]
[TD]09/03/2014[/TD]
[TD]16/03/2014[/TD]
[TD] 23/03/2014[/TD]
[TD] 30/03/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SIG[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PUB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DEL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AUD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]OTH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is where the data needs to come from:


[TABLE="width: 739"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]28/02/2014[/TD]
[TD]31/03/2014[/TD]
[TD]30/04/2014[/TD]
[TD]31/05/2014[/TD]
[TD]30/06/2014[/TD]
[TD] 31/07/2014[/TD]
[TD] 31/08/2014[/TD]
[TD] 30/09/2014[/TD]
[/TR]
[TR]
[TD]SIG[/TD]
[TD][/TD]
[TD="align: right"]57259.99[/TD]
[TD="align: right"] 59289.3333[/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"] 13333.3333[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9666.66333[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]14625.8333[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10666.67[/TD]
[/TR]
[TR]
[TD]HB PUB[/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8833.33333[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2500[/TD]
[/TR]
[TR]
[TD]MP PUB[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]EBOOK[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD][/TD]
[TD="align: right"]57759.99[/TD]
[TD="align: right"]68955.9967[/TD]
[TD="align: right"]34500[/TD]
[TD="align: right"]13833.3333[/TD]
[TD="align: right"]14625.8333[/TD]
[TD="align: right"]13833.3333[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]13166.67[/TD]
[/TR]
</tbody>[/TABLE]


I have tried numerous formulas such as lookups, INDEX & MATCH functions however all the examples seem to draw off data that is set out in columns rather than both columns & rows.

Thanks.

 

[perimidt]

Hi

SIG and DEL will be easy to lookup since they are in both tables, but what is PUB, AUD and OTH?

 

[Nicole87]

Hi

SIG and DEL will be easy to lookup since they are in both tables, but what is PUB, AUD and OTH?

Ah sorry I picked up the wrong table example so it would be related to the same types, so the first table would be

[TABLE="width: 153"]
<colgroup><col></colgroup><tbody>[TR]
[TD]UNSIGNED[/TD]
[/TR]
[TR]
[TD]SIG[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[/TR]
[TR]
[TD]HB PUB[/TD]
[/TR]
[TR]
[TD]MP PUB[/TD]
[/TR]
[TR]
[TD]EBOOK

Thanks,[/TD]
[/TR]
</tbody>[/TABLE]

 

[Charlie45]

If your 2nd table is in A1:J7, then in your 1st table enter =INDEX($A$1:$K$7,MATCH($A15,$A$1:$A$7,FALSE),MATCH(B$14,$A$1:$K$1,FALSE)) To the immediate right of the Sig and under the column containing 16/02/2014. copy down and to the right.

 

[perimidt]

Here is my SUMIF-suggestion:
=SUMIF($A$2:$A$7,$A13,B$2:B$7)

2nd table still in A1:J7 and formula in the first cell you want to calculate.

 

[Pedrumj]

Have you considered using a simple macro?

dim i as integer
dim j as integer
for i = 1 to countRows
for j = 1 to count Columns
if (cells(i, 1) = Target) and (cells(2, j) = Other Target) then
'your code
end if

next j

next i

you could also find more information regarding searching in this article:

[url =" http://www.software-solutions-online.com/#!publications-searching-data-in-excel-she/c1ksc"]Searching Data In Excel Sheets[/url]

 

[Nicole87]

If your 2nd table is in A1:J7, then in your 1st table enter =INDEX($A$1:$K$7,MATCH($A15,$A$1:$A$7,FALSE),MATCH(B$14,$A$1:$K$1,FALSE)) To the immediate right of the Sig and under the column containing 16/02/2014. copy down and to the right.

Great - thanks very much!

 

[Charlie45]

Thanks for the feedback! Glad I could help.