How to combine IF, VLOOKUP and MATCH?

**stormracela** · 05-17-2010, 12:13 AM

How do you combine IF, LOOKUP and MATCH to come up with a formula in these conditions

If A7 is equal in any rows in sheet 2 column A, B6 is equal to any rows in sheet 2 column B, and A3 is equal to any rows in C, then return the value of sheet 2 column D.

Thanks!

**teylyn** · 05-17-2010, 12:35 AM

Can you post a workbook? I'm not sure I see where you want the result, and if you want to compare row by row.

**stormracela** · 05-17-2010, 12:51 AM

okay, I did a sample workbook.

The blue lines are the ones that I want to populate value of Data Source column D.

It has to satisfy conditions that A4, B4 and C3 matched with all data of columns A, B, C from Data source.

Thanks!

**contaminated** · 05-17-2010, 01:33 AM

try this in C4
=SUMPRODUCT(($A4='Data Source'!$A$2:$A$40)*(LookUp!$B4='Data Source'!$B$2:$B$40)*(LookUp!C$3='Data Source'!$C$2:$C$40)*('Data Source'!$D$2:$D$40))

Or this one

=LOOKUP(99^99,CHOOSE({1,2},0,INDEX('Data Source'!$D$2:$D$40,MATCH($A4 & $B4 & C$3, 'Data Source'!$A$2:$A$40 & 'Data Source'!$B$2:$B$40 & 'Data Source'!$C$2:$C$40,0))))

confirm as an array formulae (<Ctrl>+<Shift>+<Enter>)

copy down then accross

**stormracela** · 05-17-2010, 01:44 AM

Why do we use sumproduct? I'm not actually adding the numbers, I just want to return it...

Thanks

**contaminated** · 05-17-2010, 01:52 AM

lol...... because SUMPRODUCT works in your case...
Moreover, I think this one better then aboveposted

=LOOKUP(99^99,CHOOSE({1,2},0,INDEX('Data Source'!$D$2:$D$40,MATCH(1,INDEX(($A4='Data Source'!$A$2:$A$40)*($B4='Data Source'!$B$2:$B$40)*(C$3='Data Source'!$C$2:$C$40),0),0))))

confirmed just with enter

**stormracela** · 05-17-2010, 01:57 AM

But why does the cells merge?

**contaminated** · 05-17-2010, 02:13 AM

see attached if you ask why it works

**stormracela** · 05-17-2010, 03:14 AM

okay, got it!

Thanks a bunch!!!

**teylyn** · 05-17-2010, 03:17 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save