Lookup in two columns for same value

[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

 

[Pete_UK]

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

 

[Dave Peterson]

=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.

 

[Don Guillett]

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