Lookup in two columns for same value

  • Thread starter Thread starter autoenthu
  • Start date Start date
A

autoenthu

Hi and happy new year to uall,

I have a problem, where I am trying to match a value in one of the
sheets (say 1) to either of the two columns in another sheet (say 2)
and then return the value to Sheet2 from the 3rd column of Sheet 1

Sheet 1:

GM MG 1
JF FJ 2
DM MD 9
JS SJ 6
JM MJ 23

Sheet 2 (Should look like this)

GM 1
MG 1
MJ 23
MD 9
 
Put this in B1 of Sheet2:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),IF(ISNA(MATCH(A1,Sheet1!B:B,0)),"not
present",INDEX(Sheet1!C:C,MATCH(A1,Sheet1!B:B,0))),INDEX(Sheet1!
C:C,MATCH(A1,Sheet1!A:A,0)))

and then copy down as required.

Hope this helps.

Pete
 
=if(isna(vlookup(a1,sheet2!a:c,3,false)),vlookup(a1,sheet2!b:c,2,false),
vlookup(a1,sheet2!a:c,3,false))

Is one way.
 
Sub getdata() 'run from sheet2 with list in col A
lr = Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
For Each c In Range("a2:a" & lr)
With Sheets("sheet1")
mr = .Cells.Find(What:=c, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row
If mr > 0 Then c.Offset(, 1) = .Cells(mr, 3)
End With
Next c
End Sub
 
Back
Top