CODEWITHSUNDEEP
excelvbaexcel-formulalookup
김지은
김지은

Reputation: 33

How do I extract a value in excel that meets multiple row and column criteria?

I have a worksheet like this, where A1:C14 records the temperature of different tanks everyday.

img

I want to make an excel formula or vba macro that is able to pull the latest temperature of each tank so that I can fill in a new table in E1:G6?

How does one go about making a code that is able to:

  1. Find the correct tank
  2. Find the latest date when each tank was measured
  3. Give out a value of the temp of that selected tank

I tried applying this formula but just got more confused:

=INDEX(,SMALL(IF(COUNTIF(,),MATCH(,),""),ROWS),COLUMNS)

Thank you all!

Upvotes: 2

Views: 274

Answers (4)

Dy.Lee
Dy.Lee

Reputation: 7567

You can also use user-defined functions.

Function myTemp(s As Range)
    Application.Volatile
    Dim vDB
    Dim myS As Single
    Dim myDay As Date
    Dim i As Long, r As Long
    
    vDB = Range("a1", Range("c" & Rows.Count).End(xlUp))

    r = UBound(vDB, 1)
    For i = 2 To r
        If vDB(i, 2) = s Then
            If vDB(i, 1) >= myDay Then
                myDay = vDB(i, 1)
                myS = vDB(i, 3)
            End If
        End If
    Next i
    myTemp = myS
End Function

enter image description here

Upvotes: 0

user12757608
user12757608

Reputation:

This is a fairly quick attempt to solve your problem..

First, make sure your date values are actually formatted as dates (ie. 08/20/2020)

then in Column F you can use:

=MAX(IF($B$2:$B$15=$E2,$A$2:$A$15))

to find the latest date for each tank read.

Then in Column G use:

=INDEX($C$2:$C$14,MATCH(1,IF($A$2:$A$14=$F2,IF($B$2:$B$14=$E2,1)),0))

to find the value for each tank on the date of the latest read.

I would suggest you make use of ranges so that you can insert lines and automatically increase the date range etc.

It might not be too hard to combine the two seperate formulas above into a single one. But this way you can see what is happening and I thought the latest read date for each tank could be useful?

Upvotes: 1

basic
basic

Reputation: 11968

In case when dates aren't sorted you can use INDEX / AGGREGATE functions:

=INDEX($C$2:$C$14,AGGREGATE(14,6,(AGGREGATE(14,6,($A$2:$A$14)*($B$2:$B$14=E2),1)=$A$2:$A$14)*(E2=$B$2:$B$14)*ROW($C$2:$C$14)-1,1))

enter image description here

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54777

If the dates in column A are sorted ascending, then you can use the following formula in cell G2 and copy down.

=LOOKUP(2,1/(B:B=E2),C:C)

Check out this article for something similar.

Upvotes: 2

Related Questions