Highline Excel Class 41: HLOOKUP ROWS Horizontal Lookup

[hersheng18]

excellent work sir ..

[ExcelIsFun]

@hersheng18 , I am glad that you like them!

[notillustrated]

honestly, you rock man. quick easy and to the point, well walked through, and throughout with the most valid practical functions simplified. well done

[ExcelIsFun]

Thanks! I am glad that the video is helpful!

[chinchonchinchon]

I did it this way and it worked!

I don't know if this is correct or not but anyway, it worked!

=HLOOKUP(C$9,C$5:G$7,ROWS(C$5:­C6),0)

[ExcelIsFun]

Just fine! or:

EXCELlent!

[JoSaCh2]

Thanks for this great video!

I have a question about handling dates in the function. I am using a cell as part of the lookup, which contains a link to a cell containing a date (formatted to display as "Sep"). However, the lookup formula does not recognize the cell containing the link to the cell containing the date. I tried manually entering the "Sep" as straight text and that seemed to work. However, not sustainable as the date cells must dynamically update.

[ExcelIsFun]

This means that you are missing a crucial basic Excel fact (most people miss this): "Formulas do not see formatting". If you search for videos with "Number Formatting" or "Formulas Do Not See Formatting", or "Date Math", I have a few videos on this topic.

However, if you explain what you are trying to do more completely, I can perhaps give you an exact solution.

My hunch is that we could use something like this:

=TEXT(A1,"mmm") where A1 has the date 9/12/2009. Then TEXT gives "Sep".

[Croz1007]

These videos are a HUGE help. I love them and can't stop referring to them.

I have one question and I am thinking that a VLOOKUP is the answer, but not sure. How can I check cells from right to left to find the last populated cell and return that cells value? These cells are months and I want to find the last updated monthly value.

thanks in advance and Thank You so much for posting these awesome videos.

[ExcelIsFun]

If your dates are in cells A1:J1 try this formula to return last value;

=LOOKUP(9.9E+307,B1:J1)

[Croz1007]

That works perfectly. I wanted to find out what the first part means. The 9.9E_307 part. I understand that the second half is the range to look in, but not clear on the first part.

[ExcelIsFun]

It is the biggest number that Excel can handle. But any really big number will work. Just watch the Highline video about the LOOKUp function and it gives all the details about this formula. Or serach for "Excel get last value in row".