• 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.

Return Values Based Upon Multiple Conditions (VLOOKUP, HLOOKUP & INDEX MATCH) Don't work!!!

Iain A

New Member
Any help would be appreciated with the attached file. I am trying to use combined VLOOKUP with HLOOKUP or INDEX MATCH Formulas but my inferior excel knowledge has got me nowhere :(
What I have is a Range of Data that has lists of Components, each component has several columns of data (BOE) and each component has a Minimum, Most Likely and Maximum value.
I have a Summary sheet that has 3 dropdown boxes where I select Components, then the column headers (BOE) then select either Minimum, Most Likely or Max and an Answers box where I want the value returned.
 

Attachments

Peter Bartholomew

Well-Known Member
= XLOOKUP( Component, Component_List, OFFSET( XLOOKUP( BOE, BOE_Heading, Data ), 0, XMATCH(Level, Levels) - 1) )
also works in Office 365 but I am not fond of the OFFSET function that moves the column returned by the inner XLOOKUP to the right.
 

Iain A

New Member
= XLOOKUP( Component, Component_List, OFFSET( XLOOKUP( BOE, BOE_Heading, Data ), 0, XMATCH(Level, Levels) - 1) )
also works in Office 365 but I am not fond of the OFFSET function that moves the column returned by the inner XLOOKUP to the right.
Thanks Peter this is invaluable info that I will put to good use.
 

Iain A

New Member
In C16, enter formula :

=VLOOKUP(C2,Data!A3:AE13,MATCH(C4,Data!B1:AE1,0)+MATCH(C6,Data!B2:D2,0),0)

Regards
Bosco
Apologies but is it possible to keep the formatting from the data table. I have put the formula into VB if that makes it easier
Code:
    Range("C10").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(R[-8]C,Data!R[-7]C[-2]:R[3]C[28],MATCH(R[-6]C,Data!R[-9]C[-1]:R[-9]C[28],)+MATCH(R[-4]C,Data!R[-8]C[-1]:R[-8]C[1],0),0)"
    Range("C2").Select
 

Peter Bartholomew

Well-Known Member
@Iain A
I was pleasantly surprised that you expressed interest in the XLOOKUP solution! An advantage of both INDEX and the recently introduced XLOOKUP formulas over VLOOKUP is that the first two return a range reference rather than a value. This means that if you place the formula
= XLOOKUP( Component, Component_List, OFFSET( XLOOKUP( BOE, BOE_Heading, Data ), 0, XMATCH(Level, Levels) - 1) )
into the 'Refers to' box in Name Manager the Name 'Result' is a range reference. This allows

Code:
Sub formatDisplayCell()
Dim rng As Range
    Application.EnableEvents = False
    With [display]
        .Interior.Color = [result].Interior.Color
        .NumberFormat = [result].NumberFormat
    End With
    Application.EnableEvents = True
End Sub
to transfer properties such as the cell colour and number formats from the lookup cell to the display cell. The procedure can be run automatically every time the BOE selection is changed using the event handler

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [BOE]) Is Nothing Then
        formatDisplayCell
    End If
End Sub
to call the format procedure.

The other change on the worksheet is that the result is returned by the formula
= Result
 

Attachments

Iain A

New Member
Thanks again Peter. This will be really useful for numerous automation issues I am having and will save a significant amount of time and reduce the amount of errors on our basis of estimates.
 
Top