ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Combining HLookups &VLookups (https://www.excelbanter.com/excel-discussion-misc-queries/201660-combining-hlookups-vlookups.html)

ZS

Combining HLookups &VLookups
 
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

Combining HLookups &VLookups
 
ZS wrote:
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))


All times are GMT +1. The time now is 03:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com