# 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

• 15.9 KB Views: 9

#### bosco_yip

##### Excel Ninja
In C16, enter formula :

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

Regards
Bosco

#### Peter Bartholomew

##### Well-Known Member
= INDEX( Data,
MATCH(Component, Component_List, 0),
MATCH(BOE, BOE_Heading, 0)+(MATCH( Level, Levels, 0) - 1) )

#### 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
Just wow thanks a lot again bosco, you sir are a gentlemen

#### 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

• 28.7 KB Views: 8

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