Offsetting Multiple VLookups with Picklists

[kantian1]

Hi All,

Have a look at the file I have attached. I am trying to use 2 picklists and 2 Vlookups so that if the 1st Picklist is selected I get the result in the 2nd Picklist and visa versa if the 2nd Picklist is selected I get the result in the 1st Picklist.

Thank for your help.

[mdmackillop]

Not without VBA, or you would create a circular loop.

[Bob Phillips]

Couldn't you just blank out one or the other and use

=IF(B3<>"",formula1,IF(B4<>"",formula2,""))

[kantian1]

Hi xld,

Can you put your idea in the attached file so I can see what you mean?

Thanks.

[Bob Phillips]

I would need more from you before I can do that as I was not clear what you would be looking up from B3 and/or B4.

[kantian1]

You've lost me, can't you just use the a,b,c and 1,2,3 data in the table?

Thanks.

[Bob Phillips]

Use it where, how?

[kantian1]

Hi,

Maybe our wires are crossed. If I select 'a' from the first Picklist I get '1' in the second Picklist, and the same with 'b' I get '2' etc.

As well as this I want the ability to select any of the numbers from the second Picklist, like '3' and get the result 'c' in the first Picklist.

Thanks.

[georgiboy]

So let me get this straight you want it so that when you select "e" in the first picklist you want the vlookup formula to display "5" and if you selected say "6" in the second picklist the vlookup formula would display "f", is that correct?

[georgiboy]

oh i see, i believe as XLD said you would get a circular reference. What is the use of the Vlookup then?

[kantian1]

Hi,

Is there no way of putting the formulas somewhere else, so the formulas are in different cells to the results?

Thanks.

[Bob Phillips]

Then you need VBA event code as MD suggested

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim idx As Long

Application.EnableEvents = False

If Target.Address = "$B$3" Then

idx = Application.Match(Target.Value, Me.Range(Right$(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)), 0)
Me.Range("B4").Value = Application.Index(Me.Range(Right$(Range("B4").Validation.Formula1, Len(Range("B4").Validation.Formula1) - 1)), idx)
ElseIf Target.Address = "$B$4" Then

idx = Application.Match(Target.Value, Me.Range(Right$(Target.Validation.Formula1, Len(Target.Validation.Formula1) - 1)), 0)
Me.Range("B3").Value = Application.Index(Me.Range(Right$(Range("B3").Validation.Formula1, Len(Range("B4").Validation.Formula1) - 1)), idx)
End If

Application.EnableEvents = True

End Sub
[/vba]

[georgiboy]

Not without VBA, or you would create a circular loop.

I meant to say MD sorry

[kantian1]

Hi xld,

That works perfect, but due to restrictions I can't use VBA. Can there possibly be answer without using VBA?

Thanks.