how to exclude the row which has lookup value in vlookup in excel automation using vb.net

Question

is there any way to use vlookup or index and match functions in Excel exclude the entire row which has lookup value in the table array in VB.NET?

For example: I have a table array like

101 2 200 500

102 5 200 450

103 3 200 750

101 2 200 450

103 5 200 500

lookup value :103 (A3)

table array:A1:D5

column:4 (500)

For example I am looking for 103 (ie A3 cell value) in the table array. I want D5 cell value. But I am getting the D3 cells value (ie the row itself): 750 instead of 500 in the 5th row. So I want to exclude the look up value of that row while searching. Is it possible? Please guide me.

i have a lot of rows with data in Excel sheet. so i am directly using vlookup with for loop. This is my code:

For ii As Integer = 0 To i20 - 1

        bmatint(ii, 0) = "=VLOOKUP( " & "B" & ii + 2 & " ,B2:R15000,1,FALSE)"
        bmatint(ii, 1) = "=VLOOKUP( " & "B" & ii + 2 & " ,B2:R15000,16,FALSE)"
        bmatint(ii, 2) = "=VLOOKUP( " & "B" & ii + 2 & " ,B2:R15000,17,FALSE)"
        bmatint(ii, 3) = "=VLOOKUP( " & "I" & ii + 2 & " ,B2:R15000,1,FALSE)"
        bmatint(ii, 4) = "=VLOOKUP( " & "I" & ii + 2 & " ,B2:R15000,16,FALSE)"
        bmatint(ii, 5) = "=VLOOKUP( " & "I" & ii + 2 & " ,B2:R15000,17,FALSE)"

gvg

i suspect that your vlookup syntex is wrong. it goes like this: VLOOKUP(" & Chr$(34) & excelSheetName " & Chr$(34) & ",RANGE,COLUMN,FALSE/TRUE) use your actual variable — Ranhot
it's working fine. but it also considering lookup value row also. that is giving worng results. — user3060904
that is because you are using COLUMN#1 in first line of your FOR loop above. bmatint(ii, 0) = "=VLOOKUP( " & "B" & ii + 2 & " ,B2:R15000,1,FALSE)" — Ranhot
how to do it correctly. exclude the row which has lookup_value. — user3060904

Ranhot Ranhot · Accepted Answer · 2014-04-25T12:01:54

you can do that with an if statement, see the pseudocode below:

if A1.Value = myValue and currentRow <> 3 then
     '''your return code
     return Column(4).CurrentRow.Value  
endif

please post your code for a detailed answer

how to exclude the row which has lookup value in vlookup in excel automation using vb.net

1 Answers