HLookup - based on Dates in row 1 and Ref column A+B

[Kelli*]

Hi, I wish I even knew how to communicate in excel terms - God only knows if this would be a Hlookup, I don't even know why I assumed this and I can't edit the post. I assume this will come in time.
I would like to return a value based on the dates in row 1 and column A+B, from the second import tab i.e.



FYI, I do go over the formulas provided and use in other scenarios to help me understand how to apply etc.

thanking you in advance,

Kelli

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Kelli*]

Thanks Narayan,

If the source data is contained in a separate file, should that make any difference to the formula used?

 

[NARAYANK991]

Thanks Narayan,

If the source data is contained in a separate file, should that make any difference to the formula used?

Hi ,

No. Instead of referring to the Source worksheet using a worksheet reference , refer to it using its complete reference inclusive of the workbook name within square brackets [].

Narayan

 

[Kelli*]

Hi ,

No. Instead of referring to the Source worksheet using a worksheet reference , refer to it using its complete reference inclusive of the workbook name within square brackets [].

Narayan

I've tried doing that & for some reason it doesn't work & it doesn't appear to copy down correctly i.e. it doesn't zero fill like it did when the source data was contained in the same spreadsheet. The only thing that changes when I go down to the next cell is the row i.e. MATCH($B6 & $C6, becomes $B7 & $C7 etc. I've only copied the formula down to cell D10 and highlighted them purple.
Cheers!

 

[Kelli*]

I've tried doing that & for some reason it doesn't work & it doesn't appear to copy down correctly i.e. it doesn't zero fill like it did when the source data was contained in the same spreadsheet. The only thing that changes when I go down to the next cell is the row i.e. MATCH($B6 & $C6, becomes $B7 & $C7 etc.
View attachment 47680

I hope that didn't sound pointed at you, my frustration is in my own inability & technology when it doesn't work lol

 

[NARAYANK991]

Hi ,

If you can upload the workbook which has the formula , I can revise it and re-upload it.

Since the formula at present is in the form of a screenshot , I am not able to copy it to a worksheet to revise it.

Narayan

 

[Kelli*]

Hi ,

If you can upload the workbook which has the formula , I can revise it and re-upload it.

Since the formula at present is in the form of a screenshot , I am not able to copy it to a worksheet to revise it.

Narayan

Not a problem, please find attached the source file - I've been slowly going through formula by formula to get the rest of the spreadsheet working. This is my last obstacle
Where are you located? or like me, work from sun up to sun down & some lol

 

[NARAYANK991]

Hi ,

See if this formula works :

=IFERROR(INDEX('[Agency hours.xls]Agency Nov 17'!$C$4:$AG$36, MATCH($A2 & $B2, '[Agency hours.xls]Agency Nov 17'!$A$4:$A$36 & '[Agency hours.xls]Agency Nov 17'!$B$4:$B$36, 0), MATCH(C$1, '[Agency hours.xls]Agency Nov 17'!$C$3:$AG$3, 0)), "")

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[Kelli*]

Hi ,

See if this formula works :

=IFERROR(INDEX('[Agency hours.xls]Agency Nov 17'!$C$4:$AG$36, MATCH($A2 & $B2, '[Agency hours.xls]Agency Nov 17'!$A$4:$A$36 & '[Agency hours.xls]Agency Nov 17'!$B$4:$B$36, 0), MATCH(C$1, '[Agency hours.xls]Agency Nov 17'!$C$3:$AG$3, 0)), "")

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

Hi Narayan,

That is perfect 'You are indeed a ninja' thanks again, I really appreciate it!

Kelli