Multiple If conditions, vlookup?....

[tex-i-can]

Hello all,

I am wokrking a 100k line spreadsheet and need some help. I am trying to display the value in column D from sheet 1 of my file to sheet 2. The catch is I need columns A, B and C to meet certain critera.


Col. A Col. B Col. C Col.D
Blue Circle Table 1000

Green Square Chair 2000

Yellow Cirlce Desk 3000

Red Square Chair 4000


What I am looking to do is display the value in column D if column A is "Green", Column B is "Square" and Column C is "Chair". I want "2000" (cell D2) to display as the answer. I dont want to have to tell it to display D2. In other words, if possible, I would like to have excel find this cell and display it. All combinations in the first 3 columns are unique to each row, meaing I wont have to worry about two rows having the same combination. If this does not make sense please let me know. Thanks

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim criteria1 As String
    Dim criteria2 As String
    Dim criteria3 As String
    criteria1 = InputBox("Please enter the color.")
    criteria2 = InputBox("Please enter the shape.")
    criteria3 = InputBox("Please enter the item.")
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim x As Long
    For x = bottomA To 2 Step -1
        If Cells(x, "A") = criteria1 And Cells(x, "B") = criteria2 And Cells(x, "C") = criteria3 Then
            MsgBox Cells(x, "D")
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[West Man]

try: SUMPRODUCT(--(A1:A1000="Green"),--(B1:b1000="square",--(C1:C1000="Chair"),D1:D1000) adjust ranges

 

[jai9]

Hi,

Try,

=IF(AND(A2="Green",B2="Square",C2="Chair"),D2,"")

Jai