I think sumproduct formulas are way to go.
This is a discussion on How to compare/match 3 columns within the Excel Questions forums, part of the Question Forums category; I've to compare two sheets with similar columns are : Sheet1: Column A (O/N), Column B ( P/N), Column C ...
I've to compare two sheets with similar columns are :
Sheet1: Column A (O/N), Column B ( P/N), Column C (QTY), Column D (Date)
Sheet2: Column A (QTY), Column B (O/N ), Column C (P/N)
All Columns are in text format except Column D which is in Date mm/dd/yy).
Here I have to compare these two sheets and return value of Coulmn D of sheet1 on Column E of sheet2 with following conditions:
-If cln A of Sht1 equal to cln B of sht2 then go to copare cln B of sht1 with cln C of sht2;
-if both match, then compare cln C of sht1 with cln A of sht2;
-if these three column are matched, return value of cln D of sht1 on cln E of sht 2
I've tried to use MATCH and INDEX formula, but fail at all. Should I use array formula to solve such conditions?
Btw, anyone can help to post formula here?
Tks a lot.
Tks for your suggestion.
But i still don't know how to make it.
Could you post formula with example sheet here?
Search for multikey lookup where an additional column is used to concatenate relevant columns.Originally Posted by clwong
Tks for your hits too.
Sorry, as I don't know what "Search for multikey lookup where an additional column is used to concatenate relevant columns." mean, could you give me more hits or a real example?
Tks in adv.