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).

7 8 9 10 11 12 13 16/02/2014 23/02/2014 28/02/2014 09/03/2014 16/03/2014 23/03/2014 30/03/2014 SIG PUB DEL AUD OTH <tbody> </tbody>

<tbody>
</tbody>

This is where the data needs to come from:

		28/02/2014	31/03/2014	30/04/2014	31/05/2014	30/06/2014	31/07/2014	31/08/2014	30/09/2014
SIG		57259.99	59289.3333	12000	13333.3333	0	0	0	0
DEL		0	9666.66333	20000	0	14625.8333	5000	0	10666.67
HB PUB		500	0	2500	500	0	8833.33333	0	2500
MP PUB		0	0	0	0	0	0	0	0
EBOOK		0	0	0	0	0	0	0	0
TOTAL		57759.99	68955.9967	34500	13833.3333	14625.8333	13833.3333	0	13166.67

<tbody>
</tbody>

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

UNSIGNED

SIG

DEL

HB PUB

MP PUB

EBOOK Thanks,

<colgroup><col></colgroup><tbody>
</tbody>

 

[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.