LOOKUP 2 DIFFERENT VALUES AT ONE TIME AND RETURN A VALUE
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) |
LOOKUP 2 DIFFERENT VALUES AT ONE TIME AND RETURN A VALUE
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) |
LOOKUP 2 DIFFERENT VALUES AT ONE TIME AND RETURN A VALUE
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) |
All times are GMT +1. The time now is 07:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com