|
Lookup / Match ?
Hi,
I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
Lookup / Match ?
Assuming that A1:F5 contains the table, H2 contains Mon, I2 contains B,
and J2 contains 3, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX($B$2:$F$5,MATCH(J2,$A$2:$A$5,0),MATCH(1,IF( $B$1:$F$1=H2,IF($B$2:$F $2=I2,1)),0)) Hope this helps! In article , Mike B wrote: Hi, I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
Lookup / Match ?
=INDEX(A1:F5,MATCH(G3,A1:A5,0),SUMPRODUCT(--(B1:F1=G1),--(B2:F2=G2),COLUMN(B1:F1)))
Where G1 - Mon, ... G2 - A,B G3 - 1,2,3 ... Regards, Stefi €˛Mike B€¯ ezt Ć*rta: Hi, I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
Lookup / Match
?Appologies for the dely in replying, this was a great helpthank you. May I
ask a further question, what would I add to this function to get the information from another worksheet in my workbook ? "Domenic" wrote: Assuming that A1:F5 contains the table, H2 contains Mon, I2 contains B, and J2 contains 3, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX($B$2:$F$5,MATCH(J2,$A$2:$A$5,0),MATCH(1,IF( $B$1:$F$1=H2,IF($B$2:$F $2=I2,1)),0)) Hope this helps! In article , Mike B wrote: Hi, I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
Lookup / Match ?
Appologies for the delay in replying , it's a great help thanks
Mike B "Stefi" wrote: =INDEX(A1:F5,MATCH(G3,A1:A5,0),SUMPRODUCT(--(B1:F1=G1),--(B2:F2=G2),COLUMN(B1:F1))) Where G1 - Mon, ... G2 - A,B G3 - 1,2,3 ... Regards, Stefi €˛Mike B€¯ ezt Ć*rta: Hi, I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
Lookup / Match
Assuming that Sheet2 contains the source data/table, try...
=INDEX('Sheet2'!$B$2:$F$5,MATCH(J2,'Sheet2'!$A$2:$ A$5,0),MATCH(1,IF('Shee t2'!$B$1:$F$1=H2,IF('Sheet2'!$B$2:$F$2=I2,1)),0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Mike B wrote: ?Appologies for the dely in replying, this was a great helpthank you. May I ask a further question, what would I add to this function to get the information from another worksheet in my workbook ? "Domenic" wrote: Assuming that A1:F5 contains the table, H2 contains Mon, I2 contains B, and J2 contains 3, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX($B$2:$F$5,MATCH(J2,$A$2:$A$5,0),MATCH(1,IF( $B$1:$F$1=H2,IF($B$2:$F $2=I2,1)),0)) Hope this helps! In article , Mike B wrote: Hi, I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
Lookup / Match ?
You are welcome! Thanks for the feedback!
Stefi €˛Mike B€¯ ezt Ć*rta: Appologies for the delay in replying , it's a great help thanks Mike B "Stefi" wrote: =INDEX(A1:F5,MATCH(G3,A1:A5,0),SUMPRODUCT(--(B1:F1=G1),--(B2:F2=G2),COLUMN(B1:F1))) Where G1 - Mon, ... G2 - A,B G3 - 1,2,3 ... Regards, Stefi €˛Mike B€¯ ezt Ć*rta: Hi, I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
Lookup / Match
Hi,
This works great when I enter the sorce worksheet directly into the function. Ie 'Sheet2', however If I enter'Week2' into a cell and enter that cell in place of 'Week2' into the fuction ,no matter how I try it will not work, any suggestions please Thanks Mike B "Domenic" wrote: Assuming that Sheet2 contains the source data/table, try... =INDEX('Sheet2'!$B$2:$F$5,MATCH(J2,'Sheet2'!$A$2:$ A$5,0),MATCH(1,IF('Shee t2'!$B$1:$F$1=H2,IF('Sheet2'!$B$2:$F$2=I2,1)),0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Mike B wrote: ?Appologies for the dely in replying, this was a great helpthank you. May I ask a further question, what would I add to this function to get the information from another worksheet in my workbook ? "Domenic" wrote: Assuming that A1:F5 contains the table, H2 contains Mon, I2 contains B, and J2 contains 3, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX($B$2:$F$5,MATCH(J2,$A$2:$A$5,0),MATCH(1,IF( $B$1:$F$1=H2,IF($B$2:$F $2=I2,1)),0)) Hope this helps! In article , Mike B wrote: Hi, I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
Lookup / Match
Use the INDIRECT function.
-- David Biddulph "Mike B" wrote in message ... Hi, This works great when I enter the sorce worksheet directly into the function. Ie 'Sheet2', however If I enter'Week2' into a cell and enter that cell in place of 'Week2' into the fuction ,no matter how I try it will not work, any suggestions please Thanks Mike B "Domenic" wrote: Assuming that Sheet2 contains the source data/table, try... =INDEX('Sheet2'!$B$2:$F$5,MATCH(J2,'Sheet2'!$A$2:$ A$5,0),MATCH(1,IF('Shee t2'!$B$1:$F$1=H2,IF('Sheet2'!$B$2:$F$2=I2,1)),0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Mike B wrote: ?Appologies for the dely in replying, this was a great helpthank you. May I ask a further question, what would I add to this function to get the information from another worksheet in my workbook ? "Domenic" wrote: Assuming that A1:F5 contains the table, H2 contains Mon, I2 contains B, and J2 contains 3, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... =INDEX($B$2:$F$5,MATCH(J2,$A$2:$A$5,0),MATCH(1,IF( $B$1:$F$1=H2,IF($B$2:$F $2=I2,1)),0)) Hope this helps! In article , Mike B wrote: Hi, I would be gratefull for any help with the following Mon Mon Tues Tues Wd 1 A B A B A 2 dave Harry Nick Ed Jack 3 Mark Ian Mike Pete Tom 4 Ian Dave Marc Harry Edd How would I lookup /Match the top two rows and 1st column to return a result from the names. ie Mon, B,3, Would give me the name Ian. Thanks Mike B |
| All times are GMT +1. The time now is 07:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com