VLOOKUP using two different columns

[Joeclupis]

Hello all,

I am trying to set up a worksheet that pulls information from another sheet. On sheet one I have columns starting with a date column (COL A). I am trying to get information from a second column (COL AA) to return to the second worksheet. However, the data in COL AA is not consistent, in that there are only just a few entries throughout the year. I am trying to get the last entry in a calendar year from COL AA.


[TABLE="class: grid, width: 25, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]1/1/19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/31/19[/TD]
[TD]$3.984544[/TD]
[/TR]
[TR]
[TD]2/22/19[/TD]
[TD]$3.04589[/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/28/19[/TD]
[TD]$2.98654[/TD]
[/TR]
[TR]
[TD]12/31/19[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On the second worksheet the correct answer would be $2.98654. What should the formula look like?

Thank you for your help.

Joseph Carney, C.M.
Airport Operations Specialist

 

[crazydragon84]

i don't get it... why can't you just use a normal vlookup, or is there more than one instances of 11/28/19?

 

[Joeclupis]

I was using that as an example. We purchase fuel when our inventory runs low, and this happens at indeterminate intervals. What I am trying to do is to capture the last instance in the year and send that information to another sheet. The last purchase may be as early as October or as late a December. Also, I am not very experienced using a VLOOKUP formula.

 

[crazydragon84]

oh understood, you want the highest date value with value in column AA. you can use =Maxifs(Column A, Column AA, ">"&0) does that work?

 

[crazydragon84]

Sorry that just gives a date. so you can do =vlookup(Maxifs(Column A, Column AA, ">"&0), columns A:AA, 27,0)

 

[Joeclupis]

Thank you.

Here is what I have using the correct sheet and column names: =VLOOKUP(MAXIFS('DAILY VALUES'!A:A,'DAILY VALUES'!BI:BI, ">"&0),A:BI,62,0) -- i am getting a #NAME ? error from the formula. The formula is in a cell in a sheet labled YEAR END VALUATION, and the data is in the sheet labeled DAILY VALUES. Did I screw it up somewhere? I will be off duty shortly and will be checking back in tomorrow.

 

[crazydragon84]

go to formula ribbon, evaluate formula, and check which part is causing the error. the only thing I can think of either the date or price column you have is a text as opposed to a number, so it can't vlookup or find max number.

 

[sandy666]

MAXIFS()

Note: This feature is available on Windows or Mac if you have Office 2019, or if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

 

[Tetra201]

... i am getting a #NAME ? error from the formula...

Most likely, you just do not have the MAXIFS in your Excel.

Try the following instead:

=LOOKUP(2,1/(YEAR('DAILY VALUES'!A2:A1000)=2019)/('DAILY VALUES'!BI2:BI1000<>""),'DAILY VALUES'!BI2:BI1000)

 

[Joeclupis]

Crazydragon84 - Thanks for all the help.
Tetra201 - Thank you for the 2nd look. I put in your formula, adjusting for correct ranges and got a #N/A error -- something that I would expect with no data for 2019 yet. I will play with it later today when I get a chance today.

Joeclupis