Vlookup help

[rwdriver]

Hello I am new to the forum, I have learn so much with the different posts I have read here.
I am using this formula:

VLOOKUP((B28),CHOOSE({1,2},H4:H14,Pilot_Name),2,0)

It works very good, it does exactly what I need it to do, but I have to change the column (H4:H14) manually for the next day.
I was wondering if there is a way to make this same formula change from column H4:H14 to the next column to the right I4:I14 which is the next day.

[abousetta]

Try this:

Please Login or Register  to view this content.

and drag to the right. The H should turn into an I and then J, etc.

[rwdriver]

Thank you very much it does change the column, but I need t copy and paste that cell to the adjacent every time, my goal is that the cell where the VLOOKUP formula is don't change, but the criteria of where to look changes according to the day. If i did it correctly you shouldhave the attachement:
lets say the formula is on cell B17, I cannot change it from here, my goal is to have it look at cell A1 which contains the formula "TODAY()" from here it should go to row where the same date as today is and look down that column(H4:H14) now the column should change to next on the right based that cell A1 will change every day. Once again thank you so much.

[abousetta]

Quick thoughts because I have to go right now.

1) Since today is volatile, the results will change each day. IF this is fine then no problem, if not then you are probably better off with vba to copy/ paste the value to the cell each day.

2) You will probably need Index/ Match but can't really say until I see at least a dummy workbook showing what you are after exactly.

[rwdriver]

Ii took me a little bit of time to figure how to attach the workbook, it should be on my previous response, I will attach it again just in case.

[oeldere]

I changed the green cells.

You work with merged cells. I advice you not to work with merged cells.

You get in trouble sooner or later.

See the example.

[rwdriver]

Thank you very much that is exactly what I need.Owesome

[oeldere]

Glad I could help. Thanks for the reply.

If the question is solved, will you mark your question as solved?

If you have (other) questions, on this item, just ask.

[Cutter]

@ rwdriver

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[rwdriver]

oeldere, I was running my spread sheet it works very good until you get to the doble letter column, when the formula adds 63+24+3 which would be character 90 (Z) is there a way to have it also include the doble letters or even triple columns?

---------- Post added at 04:57 AM ---------- Previous post was at 04:55 AM ----------

Sorry for that, I was looking where to marked solved thanks for the input I will make sure I do it next time. thanks

[oeldere]

That's a good question!

I will look for an answer.

[oeldere]

I changed it.

It works now till de date in LZ3 (is 2 july 2013)

Do you have questions on this item, just ask.

Please reply if this what you're up to.

[rwdriver]

Thanks oeldere, for some reason the change you made is giving me a reference error, it may be the refrence to the table you made, any how if I can get at least to column SZ, which would be like Jan 2015, I was thinking on the first formula you sent, if the date is >24<=31 then make it combine the characters (65=A 65&65=AA) I wrote 65 but should be the cell where the number is stored. I have been trying with IF, AND OR functions but it would have a very long formula to combine A with B, C,D ..... and then do the same with B combined with A, B, C .... and so forth until SZ, that would be just too much. I consider intermediate level knowledg with the formulas but I do not know anything on VBA,

[oeldere]

reaction 1

I don't know why, but indead the file is corrupt.

I be back soon.

reaction 2
I added the text below.

The problem seems to be that match, can match only 64 column.

I will look at www if this is a know problem.

I think it's strange i got no failure in the first place.

I will report the result.

reaction 3
I don't think the above is the problem (any more).

Please reply back if this one will do.

[martindwilson]

how do you get to lz? in a pre 2007 workbook?

[oeldere]

@Martindwilson

It's excel 2007 (not 2003).

See the fil-extention in #14

I had posted it as excel 2003 file see the file (extension) in #12.

Thanks for the question.

[ChemistB]

Try this formula in B17

=VLOOKUP((B28),CHOOSE({1,2},INDEX($G$4:$BFQ$14,,MATCH(Today,$G$2:$BFQ$2,0)),Pilot_Name),2,0)

Does that work for you?

[rwdriver]

oelder I am using excel 2010 could that be the problem, it shouldn't!!!
I am still working to find a solution, keep you posted, thanks for the help.

---------- Post added at 06:05 AM ---------- Previous post was at 05:57 AM ----------

chemistb I tried the formula but is giving an error on the INDEX array segment, don't really understand the error, may as oeldere was saying the file maybe corrup, I will re-do it again and posted as soon as I get done, thanks though for the help

[oeldere]

Did you tried the solution in #14?

For me it worked.

I use Excel 2007, but i suppose it would be also usefull in excel 2010.

[ChemistB]

Here is my formula in your spreadsheet. Perhaps you left out a comma in your INDEX

[rwdriver]

C hemistb thas so much your formula works perfect, my ariginal file was corrupted, thank you so much I believe it is what I needed,

[oeldere]

@rwdriver.

Maybe you can also reply on my post #19.