Need help with vlookup formula (multiple column range)

[proximanova]

I have a spreadsheet where I need to return "U" or "P" if the cell i'm refrencing is listed on the named range on another tab I have. The problem is that the formula I'm currently using only returns what I want if the cell matches the data from the first column, my named range has 12 columns (A2:L400 named "AllComRep"). Can someone help me modify my formula to include my whole named range?

=IF(ISNA(VLOOKUP(A2,AllComRep,1,0)),"U","P")

 

[anomie]

Hi There.

Vlookup only ever returns data from one column. You specify the column with the figure after the name of the range. In your case you have specified column 1.

There may be a function in excel which looks for a value in a range. Have a google, but if you don't find anything, you can use the following vba code.

Replace "Sheet2" with the name of the sheet where the AllComRep range lives - and then just copy all the code, as is, into the Worksheet module for the sheet you were going to put the formula in.

Then, whenever you change or enter a value in column A of the sheet you were going to put the formula in, the "U" or "P" will appear next to it as appropriate.

I hope this turns out to be overkill, but there it is if you need it.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim found As Boolean

found = False

If Target.Column = 1 Then
For Each c In Sheets("Sheet2").Range("AllComRep").Cells
If c.Value = Target.Value Then found = True
Next
If found = True Then
Target.Offset(0, 1).Value = "P"
Else
Target.Offset(0, 1).Value = "U"
End If
End If

End Sub



Cheers
Ani