I am trying to find a lookup formula that will use TWO different variables.
One value is on the horizontal row one on the vertical of a spreadsheet. It
must then lookup on a separate sheet and return a value, providing BOTH
values have been found.
eg:
Sheet 1:
6397 6398 6536 6399 6400 6401 6403

SUINT
SUDOM
ARINT
ARDOM
AMDOM
AMINT
VVDOM
SBINT
ACINT
ASDOM

Sheet 2:
TGDOM 6398 100
HPDOM 6398 250
ASDOM 6398 80
ADINT 6398 40
ZLDOM 6398 30
CODOM 6398 60
In this above example, it must lookup the values across the top (eg:
6397,6398,6536 AND the value down the side ('SUDOM") and when it finds both
of these on the 2nd sheet, return the value in the 3rd column (eg 100,250,80a)

Enter as an array formula with CTRL+SHIFT+Enter

=IF(ISNA(INDEX(Sheet2!$C$1:$C$6,MATCH(1,(Sheet1!C$ 1=Sheet2!$B$1:$B$6)*(Sheet1!$A11=Sheet2!$A$1:$A$6) ,0))),"",INDEX(Sheet2!$C$1:$C$6,MATCH(1,(Sheet1!C$ 1=Sheet2!$B$1:$B$6)*(Sheet1!$A11=Sheet2!$A$1:$A$6) ,0)))

Copy across and down.


It assumes Sheet2 table is in columns A to C and Sheet1 has numbers in row 1
and "names" in Column A

HTH

"nickipas" wrote:

I am trying to find a lookup formula that will use TWO different variables.
One value is on the horizontal row one on the vertical of a spreadsheet. It
must then lookup on a separate sheet and return a value, providing BOTH
values have been found.
eg:
Sheet 1:
6397 6398 6536 6399 6400 6401 6403

SUINT
SUDOM
ARINT
ARDOM
AMDOM
AMINT
VVDOM
SBINT
ACINT
ASDOM

Sheet 2:
TGDOM 6398 100
HPDOM 6398 250
ASDOM 6398 80
ADINT 6398 40
ZLDOM 6398 30
CODOM 6398 60
In this above example, it must lookup the values across the top (eg:
6397,6398,6536 AND the value down the side ('SUDOM") and when it finds both
of these on the 2nd sheet, return the value in the 3rd column (eg 100,250,80a)

Thanks for your help!!
it took me a while to understand what you suggested and how to incorporate
it, but evenutally I got it! so thanks!!

"Toppers" wrote:

Enter as an array formula with CTRL+SHIFT+Enter

=IF(ISNA(INDEX(Sheet2!$C$1:$C$6,MATCH(1,(Sheet1!C$ 1=Sheet2!$B$1:$B$6)*(Sheet1!$A11=Sheet2!$A$1:$A$6) ,0))),"",INDEX(Sheet2!$C$1:$C$6,MATCH(1,(Sheet1!C$ 1=Sheet2!$B$1:$B$6)*(Sheet1!$A11=Sheet2!$A$1:$A$6) ,0)))

Copy across and down.


It assumes Sheet2 table is in columns A to C and Sheet1 has numbers in row 1
and "names" in Column A

HTH

"nickipas" wrote:

I am trying to find a lookup formula that will use TWO different variables.
One value is on the horizontal row one on the vertical of a spreadsheet. It
must then lookup on a separate sheet and return a value, providing BOTH
values have been found.
eg:
Sheet 1:
6397 6398 6536 6399 6400 6401 6403

SUINT
SUDOM
ARINT
ARDOM
AMDOM
AMINT
VVDOM
SBINT
ACINT
ASDOM

Sheet 2:
TGDOM 6398 100
HPDOM 6398 250
ASDOM 6398 80
ADINT 6398 40
ZLDOM 6398 30
CODOM 6398 60
In this above example, it must lookup the values across the top (eg:
6397,6398,6536 AND the value down the side ('SUDOM") and when it finds both
of these on the 2nd sheet, return the value in the 3rd column (eg 100,250,80a)