Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 4 columns?
Hi guys,
I'm hoping someone can help me out ! I have 2 worksheets and what i need to do is compare 2 cols from one to 2 cols in another - if they match then bring across a value from an X col. It's basically what a Vlookup does but instead of comparing one col and brining acorss a value i want to make sure that two cols match before it brings across a value. So in lay terms : If Value in cell Sheet1!A1 = a value in Sheet2!$A$500 AND Then value in Sheet1!$B$1 = The value in cell D from the same row in which you found the first matching value THEN bring across the value in cell F from that same row Thanks guys and gals!! Deb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 4 columns?
Perhaps something along these lines ..
In Sheet3, Put in the formula bar for say, B1: =INDEX(Sheet1!$F$1:$F$1000,MATCH(1,(Sheet2!$A$1:$A $1000=Sheet1!A1)*(Sheet2!$D$1:$D$1000=Sheet1!B1),0 )) then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) As-is, the array formula can then be copied down col B to return correspondingly for other pairs of look-up values in Sheet1's A2:B2, A3:B3, etc Adapt the ranges (eg: Sheet2!$A$1:$A$1000) to suit. Ranges must be identical in size, and we can't use entire col references (eg: A:A, B:B, etc) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Deb" wrote: Hi guys, I'm hoping someone can help me out ! I have 2 worksheets and what i need to do is compare 2 cols from one to 2 cols in another - if they match then bring across a value from an X col. It's basically what a Vlookup does but instead of comparing one col and brining acorss a value i want to make sure that two cols match before it brings across a value. So in lay terms : If Value in cell Sheet1!A1 = a value in Sheet2!$A$500 AND Then value in Sheet1!$B$1 = The value in cell D from the same row in which you found the first matching value THEN bring across the value in cell F from that same row Thanks guys and gals!! Deb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Formula or tool to compare two columns of data | Excel Worksheet Functions | |||
Compare multiple column of data and list out common and unique component in adj columns | Excel Worksheet Functions | |||
compare 2 columns | Excel Worksheet Functions | |||
Compare 2 columns, and create a list of items that are in both lists | Excel Worksheet Functions | |||
Compare data in two columns to find duplicates. | Excel Discussion (Misc queries) |