Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing cells when they are not exactly the same | Excel Discussion (Misc queries) | |||
comparing two spreadsheets using vlookup and compare | Excel Worksheet Functions | |||
comparing cells | Excel Worksheet Functions | |||
Comparing Cells | Excel Worksheet Functions | |||
Vlookup - Comparing two lists in different worksheets | Excel Worksheet Functions |