• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index - Offset - Match (Double H Lookup)?

M

Mike Langlois

Member
Hi all,

I was wondering if it is possible to have a formula that looks at the date in sheet Front, Cell B1, and find that date in sheet Data column A, and then pull back the number for the relevant letter from sheet Data, column F?

I have used Index Offset Match before, but can't seem to get it to work in this occasion?

Example attached to hopefully explain a bit more.

Thanks!
 

Attachments

Hi Narayan,

That works perfectly, thank you very much.

Is there any difference to what you have provided compared to Deepak, does one have limitations etc?

Or are they just two solutions to the same problem?

Thanks,
Mike
 
Hi Mike ,

I would say that INDIRECT should be used only when everything else fails !

Otherwise , I would say , go with which ever formula you are comfortable with. Finally , you should understand the formula well enough to modify it in case it is required.

Another criterion you can use is which formula has a lesser number of functions ; as long as two formulae do the job equally satisfactorily , choose the one which uses a lesser number of functions or simpler functions or one which can be extended should the requirement arise.

Narayan
 
I'd like to piggy back off this questions because I feel that my issue is in a similar nature to this one if no one minds.
I'm looking retrieve information from the data tab to show up on the "Table" tab in the appropriate "Previous Month" & "Current Month" columns all factored off the month end indicator. So as I currently have it set for June the current month should retrieve June's data. Is it possible to create a formula to populate the previous month's data based on the same month end indicator? The index match thing is new but I think this is the correct path to take.
 

Attachments

Zach said:
I'd like to piggy back off this questions because I feel that my issue is in a similar nature to this one if no one minds.
I'm looking retrieve information from the data tab to show up on the "Table" tab in the appropriate "Previous Month" & "Current Month" columns all factored off the month end indicator. So as I currently have it set for June the current month should retrieve June's data. Is it possible to create a formula to populate the previous month's data based on the same month end indicator? The index match thing is new but I think this is the correct path to take.
Click to expand...
Try…………

1] Previous Month C7, copy down:

=VLOOKUP(A7,Data!$A$3:$J$20,MATCH(TEXT(TODAY(),"mmmm"),Data!$A$2:$J$2,0)-1,0)

2] Current Month D7, copy down:

=VLOOKUP(A7,Data!$A$3:$J$20,MATCH(TEXT(TODAY(),"mmmm"),Data!$A$2:$J$2,0),0)

Regards
Bosco
 

Attachments

Hi all,

Thank you for your help with this, although seem to have run into a particular problem. When I have the data in the data sheet, and I add a new date range, there is a chance the names will not be in the same order / some will be missing. Currently the formula by Narayan sticks to the order within the first date table, which makes the data incorrect on dates when the agent order isn't the same.

Is there a way to correct this?

Thanks,
Mike
 
bosco_yip said:
Try…………

1] Previous Month C7, copy down:

=VLOOKUP(A7,Data!$A$3:$J$20,MATCH(TEXT(TODAY(),"mmmm"),Data!$A$2:$J$2,0)-1,0)

2] Current Month D7, copy down:

=VLOOKUP(A7,Data!$A$3:$J$20,MATCH(TEXT(TODAY(),"mmmm"),Data!$A$2:$J$2,0),0)

Regards
Bosco
Click to expand...

This works. Appreciate the help!
 
You must log in or register to reply here.
Back
Top