Search Function: Two field values against two column arrays, return a value

**AackwardsBassLogic** · 05-23-2012, 05:16 PM

Hi All,

I'm trying to write a function that takes a value in A1 and B1 from sheet 1, searches for it in sheet 2 A:A and B:B, and returns the value in sheet 2 column C that the value from A1 and B1 are found in sheet 1 A:B

For example;

Sheet 1: A1= Virtual, B1 = Microsoft, C1 = formula, D1 = 5

Sheet 2: Virtual (A45) and Microsoft(B45) are found, formula returns the value in C45 = $500.

I want the formula to then take the value ($500) and multiply it by D1(5) to produce a total value of $2500 in C1.

I'm really stumped with this one, any help is appreciated!

**daddylonglegs** · 05-23-2012, 05:46 PM

Try this

=LOOKUP(2,1/(Sheet2!A$2:A$1000=A1)/(Sheet2!B$2:B$1000=B1),Sheet2!C$2:C$1000)*D1

**AackwardsBassLogic** · 05-23-2012, 06:10 PM

Originally Posted by daddylonglegs

Try this

=LOOKUP(2,1/(Sheet2!A$2:A$1000=A1)/(Sheet2!B$2:B$1000=B1),Sheet2!C$2:C$1000)*D1

I inputted the below, T2 being A1, V2 being B1 and K2 being D1 but it seems to only like to grab some of the values? Also, it really takes a lot of processing power to compute in this workbook. Any suggestions?

=LOOKUP(2,1/(qryAMSTandEDefTable!$A:$A=T2)/(qryAMSTandEDefTable!$B:$B=V2),(qryAMSTandEDefTable!$C:$C)*K2)

**Haseeb Avarakkan** · 05-23-2012, 10:18 PM

If you use whole column A:A, there are > million cells in a column, So excel calculating million cells. limit the range like A1:A1000 as DLL shows, or use a dynamic range.

If value appear only one time use SUMIFS,

=SUMIFS(qryAMSTandEDefTable!$C:$C,qryAMSTandEDefTable!$A:$A,T2,qryAMSTandEDefTable!$B:$B,V2)*K2