VLOOKUP without Concatenating All Possible Lookup Values

[DashboardNovice]

In the attached file, on the tab called 'Timesheet Hours,' in column F, I have a VLOOKUP which I am using to reference the first tab, to bring over any hours paid.

To get the correction number of paid hours, I need to pull hours by Employee + Week Ending Date + Month Ending + Task Code. On the first tab, I created a concatenated value which will join Employee + Week Ending Date + Month Ending + Task Code. By doing this, each row on the first tab now has the proper lookup value. These are in the red cells.

I use this technique a lot (creating the concatenated field in red cells), but I don't like how it looks. I know I could hide the red column, but is there any other way to make my VLOOKUP work, without creating the concatenated field in red cells?

 

[Villalobos]

Hi,

Check out this INDEX-MATCH array proposal.

 

[DashboardNovice]

Hi,

Check out this INDEX-MATCH array proposal.

This is great. Thank you again. I'm not too good with arrays but this definitely works.

 

[bosco_yip]

Another option,

In F2, formula (non-array) copy down :

=LOOKUP(1,0/(MMULT(COUNTIF(A2:D2,Paid!B$2:E$7),{1;1;1;1})=4),Paid!F$2:F$7)

Regards
Bosco

 

[DashboardNovice]

Hi,

Check out this INDEX-MATCH array proposal.

When I tried applying your solution to the spreadsheet I'm actually working on, the row numbers on my lookup value for the match function stays fixed on row #2, i.e. the row numbers do not increase as you go down the column with the formula. Why is this? I don't even have $ in front of the row number.

I tried to simply copy down the array formula and I got an error saying "can't change part of an array."

 

[Nebu]

Hi:

If you want to use index function with out array you can use the following formula.

=INDEX(Paid!$F$2:$F$7,MATCH(1,MMULT((A2=Paid!$B$2:$B$7)*(B2=Paid!$C$2:$C$7)*(C2=Paid!$D$2:$D$7)*(D2=Paid!$E$2:$E$7),1),0),1)

Thanks

 

[bosco_yip]

If you want to use index function with out array you can use the following formula.
=INDEX(Paid!$F$2:$F$7,MATCH(1,MMULT((A2=Paid!$B$2:$B$7)*(B2=Paid!$C$2:$C$7)*(C2=Paid!$D$2:$D$7)*(D2=Paid!$E$2:$E$7),1),0),1)

An alternative to use Index function without array, and in shorter version:

=INDEX(Paid!F$2:F$7,MATCH(4,MMULT(--(A2:D2=Paid!B$2:E$7),{1;1;1;1}),))

Regards
Bosco