Combining HLookups &VLookups

[ZS]

I'm not sure if this is possible but in theory it seems quite simply.

I want to look up a date (in the top row) and a company name (in the left
hand column) If a combination of these two are found in one of three other
sheets then the value in the corresponding cell with be returned.

i.e. Returning sheet:


Jan-05 Feb-05 Mar-05 Apr-05 May-05
Company A
Company B
Company C
Company D

The three tabs to look data up in have each years data, by month, for 2005,
2006, 2007.

Ie tab 1 has 2005 data:

Jan-05 Feb-05 Mar-05 Apr-05 May-05
Company A
Company B
Company C
Company D

i.e. tab 2 has 2006 data:

Jan-06 Feb-06 Mar-06 Apr-06 May-06
Company A
Company B
Company C
Company D

 

[smartin]

I'm not sure if this is possible but in theory it seems quite simply.

I want to look up a date (in the top row) and a company name (in the left
hand column) If a combination of these two are found in one of three other
sheets then the value in the corresponding cell with be returned.

i.e. Returning sheet:


Jan-05 Feb-05 Mar-05 Apr-05 May-05
Company A
Company B
Company C
Company D

The three tabs to look data up in have each years data, by month, for 2005,
2006, 2007.

Ie tab 1 has 2005 data:

Jan-05 Feb-05 Mar-05 Apr-05 May-05
Company A
Company B
Company C
Company D

i.e. tab 2 has 2006 data:

Jan-06 Feb-06 Mar-06 Apr-06 May-06
Company A
Company B
Company C
Company D

Try INDEX/MATCH.

This will return values to sheet "Compile" from one other worksheet
"ZS1". Expand ranges/adjust sheet names to suit:

=INDEX(ZS1!$B$3:$C$6,MATCH(Compile!$A3,ZS1!$A$3:$A$6,0),MATCH(Compile!B$2,ZS1!$B$2:$C$2,0))