How to lookup the value of three cells and return the value of another cell.

A

amattey

New Member
Joined
Jul 18, 2011
Messages
46
Hi,

I am having trouble trying to create a vlookup formula to find the value of three cells in a sheet and if all threee matches in another sheet, return a value from the row on the second sheet. If anyone can help me it will be much appreciate it.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Welcome to MrExcel.

Can we have some sheet and cell references please? Can the combination of the three cells occur only once on the sheet you want to look up?
 
Upvote 0
Welcome to the Board.

Are you looking for something like this (I put it all on one sheet so you can see the formula and table easier)?
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 51px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Name1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Name2</TD><TD>Name3</TD><TD>Amount</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Bob</TD><TD>Joe</TD><TD>Fred</TD><TD style="TEXT-ALIGN: right">100</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Bob</TD><TD>Joe</TD><TD>Tom</TD><TD style="TEXT-ALIGN: right">200</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Name1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Name2</TD><TD>Name3</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Bob</TD><TD>Joe</TD><TD>Tom</TD><TD style="TEXT-ALIGN: right">200</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D7</TD><TD>=SUMPRODUCT((A2:A3=A7)*(B2:B3=B7)*(C2:C3=C7)*(D2:D3))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 
Upvote 0
Andrew Poulsom said:
Welcome to MrExcel.

Can we have some sheet and cell references please? Can the combination of the three cells occur only once on the sheet you want to look up?
Click to expand...


For example lets say I got cells A2, B2, and C2 on sheet 1. Then I got the exact same cells (these are the unique cells that identify the rest of the information) on sheet 2. I want to create a formula on cell D2 sheet 1, to look for a match of cells A2, B2, and C2 from sheet 1 on sheet 2, and if found extratc the info from cell D2 on sheet 2 and put it back in sheet 1.

Thank you for the welcome, I appreciate you guys help!
 
Upvote 0
Gledister's suggestion of CONCATENATE will work. Or you can try these formulas.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Name1</TD><TD>Name2</TD><TD>Name3</TD><TD>Amount1</TD><TD>Amount2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Bob</TD><TD>Joe</TD><TD>Tom</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">2000</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=SUMPRODUCT((Sheet2!$A$2:$A$3=Sheet1!$A$2)*(Sheet2!$B$2:$B$3=Sheet1!$B$2)*(Sheet2!$C$2:$C$3=Sheet1!$C$2)*(Sheet2!D$2:D$3))</TD></TR><TR><TD>E2</TD><TD>=SUMPRODUCT((Sheet2!$A$2:$A$3=Sheet1!$A$2)*(Sheet2!$B$2:$B$3=Sheet1!$B$2)*(Sheet2!$C$2:$C$3=Sheet1!$C$2)*(Sheet2!E$2:E$3))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Name1</TD><TD>Name2</TD><TD>Name3</TD><TD>Amount1</TD><TD>Amount2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Bob</TD><TD>Joe</TD><TD>Fred</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">1000</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Bob</TD><TD>Joe</TD><TD>Tom</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">2000</TD></TR></TBODY></TABLE>
 
Upvote 0
amattey said:
For example lets say I got cells A2, B2, and C2 on sheet 1. Then I got the exact same cells (these are the unique cells that identify the rest of the information) on sheet 2. I want to create a formula on cell D2 sheet 1, to look for a match of cells A2, B2, and C2 from sheet 1 on sheet 2, and if found extratc the info from cell D2 on sheet 2 and put it back in sheet 1.

Thank you for the welcome, I appreciate you guys help!
Click to expand...
Try this...

Data table:

Book1
ABCD
2XGCJunk
3ABCResult
4BACStuff
5CBASome
6DDDTrash
Sheet2

Lookup values:

Book1
ABCD
2ABCResult
Sheet1

This array formula** entered in D2:

=INDEX(Sheet2!D2:D6,MATCH(A2,IF(Sheet2!B2:B6=B2,IF(Sheet2!C2:C6=C2,Sheet2!A2:A6)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Using IF Statement to return three possible solutions
Replies
7
Views
396
maddogg126
M
W
  • Question Question
Move Row to Another Sheet Based on Multiple Cell Values
Replies
9
Views
578
jolivanes
jolivanes
A
  • Solved
Target.address and VLOOKUP with a range of cells
Replies
8
Views
388
RoryA
RoryA
S
  • Question Question
Multiple columns lookup
Replies
0
Views
284
Sipirili2504
S
E
  • Question Question
Lookup 2/1 works on one line and not on others
Replies
5
Views
162
Fluff
Fluff

Forum statistics

Threads
1,224,586
Messages
6,179,716
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top