Comparing 2 cells using vlookup
 

I am attempting to compare 2 cells in one file and bring another cell over if
true.

In my 1st file I have a number Cell a2 = 988881, Cell b2 = 203, d2 = 204, f2
= 205. In my 2nd file I have the same scenario except b2 can equal 203, 204,
205. I want to do a vlookup into my 1st file and once it finds 988881 then go
to cell B2 and match the number (203, 204, or 205). If it matches both
988881 and 203 then bring cell c2 over, if it doesn't then ignore and go to
d2, if it doesn't again go to f2. Once it finds a match in one of those 3
cells, pull the data from the following cell C2, E2 or G2.


1st File
A2 B2
988881 204

2nd File
A2 B2 C2 D2 E2 F2 G2
988881 203 .444 204 1.25 205 .85

Thanks in advance for any help!
--
Baltimore Ravens

Comparing 2 cells using vlookup
 

try this idea.

Sub getnumberfromcol()
On Error Resume Next
For Each x In Sheets("sheet12").Range("a2:a4")
lr = Cells(Rows.Count, "a").End(xlUp).Row
With Sheets("sheet13").Range("a2:a" & lr)
Set c = .Find(x)
If Not c Is Nothing Then
firstAddress = c.Address
Do
x.Offset(, 2) = c.Offset(, Application.Match( _
x.Offset(, 1), Sheets("sheet13").Rows(c.Row)))

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next x
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ravens Fan" wrote in message
...
I am attempting to compare 2 cells in one file and bring another cell over
if
true.

In my 1st file I have a number Cell a2 = 988881, Cell b2 = 203, d2 = 204,
f2
= 205. In my 2nd file I have the same scenario except b2 can equal 203,
204,
205. I want to do a vlookup into my 1st file and once it finds 988881 then
go
to cell B2 and match the number (203, 204, or 205). If it matches both
988881 and 203 then bring cell c2 over, if it doesn't then ignore and go
to
d2, if it doesn't again go to f2. Once it finds a match in one of those 3
cells, pull the data from the following cell C2, E2 or G2.


1st File
A2 B2
988881 204

2nd File
A2 B2 C2 D2 E2 F2 G2
988881 203 .444 204 1.25 205 .85

Thanks in advance for any help!
--
Baltimore Ravens

Comparing 2 cells using vlookup
 

Another play, using a formula

Assume this is in Sheet1
A2 B2 C2 D2 E2 F2 G2
988881 203 .444 204 1.25 205 .85

and you have in Sheet2,
A2 B2
988881 204

In Sheet2,

You could place this in C2:
=INDEX(OFFSET(Sheet1!$1:$1,MATCH(A2,Sheet1!A:A,0)-1,),MATCH(B2,OFFSET(Sheet1!$1:$1,MATCH(A2,Sheet1!A :A,0)-1,),0)+1)

C2 will return the required result from Sheet1, eg: 1.25 (for B2 = 204)
If B2 contains: 205, C2 will return: 0.85
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ravens Fan" wrote:
I am attempting to compare 2 cells in one file and bring another cell over if
true.

In my 1st file I have a number Cell a2 = 988881, Cell b2 = 203, d2 = 204, f2
= 205. In my 2nd file I have the same scenario except b2 can equal 203, 204,
205. I want to do a vlookup into my 1st file and once it finds 988881 then go
to cell B2 and match the number (203, 204, or 205). If it matches both
988881 and 203 then bring cell c2 over, if it doesn't then ignore and go to
d2, if it doesn't again go to f2. Once it finds a match in one of those 3
cells, pull the data from the following cell C2, E2 or G2.


1st File
A2 B2
988881 204

2nd File
A2 B2 C2 D2 E2 F2 G2
988881 203 .444 204 1.25 205 .85

Thanks in advance for any help!
--
Baltimore Ravens

Comparing 2 cells using vlookup
 

I used your code to pull the data. It worked when I setup a little test file,
but, when I used it on the actual file I'm trying to match, I get #N/A. The
only difference is they are different columns that I'm trying to match up.

In "sheet2" I'm trying to match data in columns "A" and "L"

and

In "sheet1" I'm trying to match up with columns "A" and "F" to sheet1
columns "A" and "L". Below is the code I used. I'm not totally familiar with
excel, so I'm sure I'm missing something. It's a learn as I go thing.
Anything you can help me with, would greatly be appreciate.

=INDEX(OFFSET(Sheet1!1:1,MATCH(A2,Sheet1!A:A,0)-1,),MATCH(L2,OFFSET(Sheet1!1:1,MATCH(L2,Sheet1!F:F ,0)-1,),0)+1)
--
Baltimore Ravens


"Max" wrote:

Another play, using a formula

Assume this is in Sheet1
A2 B2 C2 D2 E2 F2 G2
988881 203 .444 204 1.25 205 .85

and you have in Sheet2,
A2 B2
988881 204

In Sheet2,

You could place this in C2:
=INDEX(OFFSET(Sheet1!$1:$1,MATCH(A2,Sheet1!A:A,0)-1,),MATCH(B2,OFFSET(Sheet1!$1:$1,MATCH(A2,Sheet1!A :A,0)-1,),0)+1)

C2 will return the required result from Sheet1, eg: 1.25 (for B2 = 204)
If B2 contains: 205, C2 will return: 0.85
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ravens Fan" wrote:
I am attempting to compare 2 cells in one file and bring another cell over if
true.

In my 1st file I have a number Cell a2 = 988881, Cell b2 = 203, d2 = 204, f2
= 205. In my 2nd file I have the same scenario except b2 can equal 203, 204,
205. I want to do a vlookup into my 1st file and once it finds 988881 then go
to cell B2 and match the number (203, 204, or 205). If it matches both
988881 and 203 then bring cell c2 over, if it doesn't then ignore and go to
d2, if it doesn't again go to f2. Once it finds a match in one of those 3
cells, pull the data from the following cell C2, E2 or G2.


1st File
A2 B2
988881 204

2nd File
A2 B2 C2 D2 E2 F2 G2
988881 203 .444 204 1.25 205 .85

Thanks in advance for any help!
--
Baltimore Ravens

Comparing 2 cells using vlookup
 

Try this adaptation:
=INDEX(OFFSET(Sheet1!$1:$1,MATCH(A2,Sheet1!A:A,0)-1,),MATCH(L2,OFFSET(Sheet1!$1:$1,MATCH(A2,Sheet1!A :A,0)-1,),0)+1)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ravens Fan" wrote:
I used your code to pull the data. It worked when I setup a little test file,
but, when I used it on the actual file I'm trying to match, I get #N/A. The
only difference is they are different columns that I'm trying to match up.

In "sheet2" I'm trying to match data in columns "A" and "L"

and

In "sheet1" I'm trying to match up with columns "A" and "F" to sheet1
columns "A" and "L". Below is the code I used. I'm not totally familiar with
excel, so I'm sure I'm missing something. It's a learn as I go thing.
Anything you can help me with, would greatly be appreciate.

=INDEX(OFFSET(Sheet1!1:1,MATCH(A2,Sheet1!A:A,0)-1,),MATCH(L2,OFFSET(Sheet1!1:1,MATCH(L2,Sheet1!F:F ,0)-1,),0)+1)
--
Baltimore Ravens