vlookup for 2 criterias
 

Good morning,

I'm trying to use VLookup to populate the second table below using the data
from table 1, but there are two lookup matches that need to occur (Segment &
ReportType) and I am unable to make it work.

TABLE 1 : a1:c9
Segment/ReportType/Hours
AAA/Break/4620
AAA/EmpEng/690
BBB/Break/2435
BBB/Coach/16285
CCC/Coach/50744
CCC/ Empeng/2940

TABLE 2 : e1:I5
Segment/BREAK/COACH/EMPENG
ASC
BBB
BCB
CCC

This is the formula I'm using for one criteria and have tried to alter for
two criterias but without any succes..
=IF(ISERROR(VLOOKUP(K$360,wksheetHRS,2,FALSE)),"0" ,VLOOKUP(K$360,wksheetHRS,2,FALSE))

Thanks

vlookup for 2 criterias
 

In table 1 insert a new column C and put this formula in C2:

=A2&B2

with a suitable heading in C1. Copy this down to C9.

Then your formula in G2 becomes:

=IF(ISNA(VLOOKUP($F2&G$1,$C$2:$D$9,2,0)),"",VLOOKU P($F2&G$1,$C$2:$D
$9,2,0))

Copy this across to I2, and then copy G2:I2 down to row 5.

Hope this helps.

Pete



On Apr 10, 4:19*pm, Dode wrote:
Good morning,

I'm trying to use VLookup to populate the second table below using the data
from table 1, but there are two lookup matches that need to occur (Segment &
ReportType) and I am unable to make it work. *

TABLE 1 : * a1:c9
Segment/ReportType/Hours
AAA/Break/4620
AAA/EmpEng/690
BBB/Break/2435
BBB/Coach/16285
CCC/Coach/50744
CCC/ Empeng/2940

TABLE 2 : e1:I5
Segment/BREAK/COACH/EMPENG
ASC
BBB
BCB
CCC

This is the formula I'm using for one criteria and have tried to alter for
two criterias but without any succes..
=IF(ISERROR(VLOOKUP(K$360,wksheetHRS,2,FALSE)),"0" ,VLOOKUP(K$360,wksheetHRS*,2,FALSE))

Thanks

vlookup for 2 criterias
 

Thanks for taking the time to respond. Due to the set up of the report I
could not concantenate the columns, however, I was able to resolve with this
formula :
=INDEX(Worksheet!$M$250:$M$351,MATCH(1,($B361=Work sheet!$K$250:$K$351)*(D$359=Worksheet!$L$250:$L$35 1),0))

thanks!

"Pete_UK" wrote:

In table 1 insert a new column C and put this formula in C2:

=A2&B2

with a suitable heading in C1. Copy this down to C9.

Then your formula in G2 becomes:

=IF(ISNA(VLOOKUP($F2&G$1,$C$2:$D$9,2,0)),"",VLOOKU P($F2&G$1,$C$2:$D
$9,2,0))

Copy this across to I2, and then copy G2:I2 down to row 5.

Hope this helps.

Pete



On Apr 10, 4:19 pm, Dode wrote:
Good morning,

I'm trying to use VLookup to populate the second table below using the data
from table 1, but there are two lookup matches that need to occur (Segment &
ReportType) and I am unable to make it work.

TABLE 1 : a1:c9
Segment/ReportType/Hours
AAA/Break/4620
AAA/EmpEng/690
BBB/Break/2435
BBB/Coach/16285
CCC/Coach/50744
CCC/ Empeng/2940

TABLE 2 : e1:I5
Segment/BREAK/COACH/EMPENG
ASC
BBB
BCB
CCC

This is the formula I'm using for one criteria and have tried to alter for
two criterias but without any succes..
=IF(ISERROR(VLOOKUP(K$360,wksheetHRS,2,FALSE)),"0" ,VLOOKUP(K$360,wksheetHRSÂ*,2,FALSE))

Thanks

vlookup for 2 criterias
 

Okay, well I'm glad that you got something that worked for you. Thanks for
feeding back.

Pete

"Dode" wrote in message
...
Thanks for taking the time to respond. Due to the set up of the report I
could not concantenate the columns, however, I was able to resolve with
this
formula :
=INDEX(Worksheet!$M$250:$M$351,MATCH(1,($B361=Work sheet!$K$250:$K$351)*(D$359=Worksheet!$L$250:$L$35 1),0))

thanks!

"Pete_UK" wrote:

In table 1 insert a new column C and put this formula in C2:

=A2&B2

with a suitable heading in C1. Copy this down to C9.

Then your formula in G2 becomes:

=IF(ISNA(VLOOKUP($F2&G$1,$C$2:$D$9,2,0)),"",VLOOKU P($F2&G$1,$C$2:$D
$9,2,0))

Copy this across to I2, and then copy G2:I2 down to row 5.

Hope this helps.

Pete



On Apr 10, 4:19 pm, Dode wrote:
Good morning,

I'm trying to use VLookup to populate the second table below using the
data
from table 1, but there are two lookup matches that need to occur
(Segment &
ReportType) and I am unable to make it work.

TABLE 1 : a1:c9
Segment/ReportType/Hours
AAA/Break/4620
AAA/EmpEng/690
BBB/Break/2435
BBB/Coach/16285
CCC/Coach/50744
CCC/ Empeng/2940

TABLE 2 : e1:I5
Segment/BREAK/COACH/EMPENG
ASC
BBB
BCB
CCC

This is the formula I'm using for one criteria and have tried to alter
for
two criterias but without any succes..
=IF(ISERROR(VLOOKUP(K$360,wksheetHRS,2,FALSE)),"0" ,VLOOKUP(K$360,wksheetHRS*,2,FALSE))

Thanks