|
|
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
On Dec 31, 1:22 pm, autoen...[ at ]gmail.com wrote:
[Quoted Text] > 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
|
|
=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.
autoenthu[ at ]gmail.com wrote:
[Quoted Text] >
> 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
--
Dave Peterson
|
|
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1[ at ]austin.rr.com
<autoenthu[ at ]gmail.com> wrote in message
news:592ed689-9a09-40ec-8aed-49642458c166[ at ]q37g2000vbn.googlegroups.com...
[Quoted Text] > 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
>
|
|
|