Help with Match and Vlookup

[elchenuk]

Hi I'm trying to do the following. I have a sheet 1 with 7 columns and then another sheet 2 with 4 columns

sheet 1
<table border="0" cellpadding="0" cellspacing="0" width="448"><col style="width: 48pt;" span="7" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">Phrase</td> <td style="width: 48pt;" width="64">Amount</td> <td style="width: 48pt;" width="64">Variable1</td> <td style="width: 48pt;" width="64">Variable2</td> <td style="width: 48pt;" width="64">Variable3</td> <td style="width: 48pt;" width="64">Variable4</td> <td style="width: 48pt;" width="64">Variable5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">text1</td> <td align="right">1000</td> <td align="right">123</td> <td align="right">345</td> <td align="right">654</td> <td align="right">345</td> <td align="right">234</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">text2</td> <td align="right">324234</td> <td align="right">335</td> <td align="right">987</td> <td align="right">345</td> <td align="right">765</td> <td align="right">654</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">text3</td> <td align="right">234</td> <td align="right">123</td> <td align="right">567</td> <td align="right">176</td> <td align="right">456</td> <td align="right">765</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">text4</td> <td align="right">324</td> <td align="right">435</td> <td align="right">324</td> <td align="right">897</td> <td align="right">876</td> <td align="right">555</td> </tr> </tbody></table>

Sheet 2
<table border="0" cellpadding="0" cellspacing="0" width="288"><col style="width: 48pt;" width="64"> <col style="width: 72pt;" width="96"> <col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">Phrase</td> <td style="width: 72pt;" width="96">Site</td> <td style="width: 48pt;" width="64">Variable</td> <td style="width: 48pt;" width="64">Result</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">text1</td> <td>www.site1.com</td> <td>Variable1</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">text3</td> <td>www.site2.com</td> <td>Variable3</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">text2</td> <td>www.site3.com</td> <td>Variable2</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">text4</td> <td>www.site4.com</td> <td>Variable5</td> <td>
</td> </tr> </tbody></table>
The column Result is empty and would like to fill in the value of the phrase and the variable from Sheet 1.
So for example in the first row in Sheet 2, the first cell in column Result would be '123' because I am simply looking for the value of Phrase text1 and column variable1 in sheet 1.

Any help would be appreciated.

eddy

PS I apologise for re-posting this but the original post made not sense at all!

 

[T. Valko]

Hi I'm trying to do the following. I have a sheet 1 with 7 columns and then another sheet 2 with 4 columns

sheet 1
<TABLE cellSpacing=0 cellPadding=0 width=448 border=0><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt" width=64 height=17>Phrase</TD><TD style="WIDTH: 48pt" width=64>Amount</TD><TD style="WIDTH: 48pt" width=64>Variable1</TD><TD style="WIDTH: 48pt" width=64>Variable2</TD><TD style="WIDTH: 48pt" width=64>Variable3</TD><TD style="WIDTH: 48pt" width=64>Variable4</TD><TD style="WIDTH: 48pt" width=64>Variable5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>text1</TD><TD align=right>1000</TD><TD align=right>123</TD><TD align=right>345</TD><TD align=right>654</TD><TD align=right>345</TD><TD align=right>234</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>text2</TD><TD align=right>324234</TD><TD align=right>335</TD><TD align=right>987</TD><TD align=right>345</TD><TD align=right>765</TD><TD align=right>654</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>text3</TD><TD align=right>234</TD><TD align=right>123</TD><TD align=right>567</TD><TD align=right>176</TD><TD align=right>456</TD><TD align=right>765</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>text4</TD><TD align=right>324</TD><TD align=right>435</TD><TD align=right>324</TD><TD align=right>897</TD><TD align=right>876</TD><TD align=right>555</TD></TR></TBODY></TABLE>

Sheet 2
<TABLE cellSpacing=0 cellPadding=0 width=288 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 72pt" width=96><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt" width=64 height=17>Phrase</TD><TD style="WIDTH: 72pt" width=96>Site</TD><TD style="WIDTH: 48pt" width=64>Variable</TD><TD style="WIDTH: 48pt" width=64>Result</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>text1</TD><TD>www.site1.com</TD><TD>Variable1</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>text3</TD><TD>www.site2.com</TD><TD>Variable3</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>text2</TD><TD>www.site3.com</TD><TD>Variable2</TD><TD>

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="HEIGHT: 12.75pt" height=17>text4</TD><TD>www.site4.com</TD><TD>Variable5</TD><TD>

</TD></TR></TBODY></TABLE>
The column Result is empty and would like to fill in the value of the phrase and the variable from Sheet 1.
So for example in the first row in Sheet 2, the first cell in column Result would be '123' because I am simply looking for the value of Phrase text1 and column variable1 in sheet 1.

Any help would be appreciated.

eddy

PS I apologise for re-posting this but the original post made not sense at all!

Try this...

Lookup table:

Book1
	A	B	C	D	E	F	G
1	Phrase	Amount	Variable1	Variable2	Variable3	Variable4	Variable5
2	text1	1000	123	345	654	345	234
3	text2	324234	335	987	345	765	654
4	text3	234	123	567	176	456	765
5	text4	324	435	324	897	876	555
Sheet1

Summary table:

Book1
	A	B	C	D
1	text1	www.site1.com	Variable1	123
2	text3	www.site2.com	Variable3	176
3	text2	www.site3.com	Variable2	987
4	text4	www.site4.com	Variable5	555
Sheet2

This formula entered in D1 and copied down:

=VLOOKUP(A1,Sheet1!A$1:G$5,MATCH(C1,Sheet1!A$1:G$1,0),0)

 

[tweedle]

I think INDEX(RANGE,MATCH,MATCH) may be what you seek.


<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: 80px"><COL style="WIDTH: 139px"><COL style="WIDTH: 113px"><COL style="WIDTH: 68px"></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 style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Phrase</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Site</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Variable</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Result</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">text1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">www.site1.com</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Variable1</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">123</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">text3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">www.site2.com</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Variable3</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">176</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">text2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">www.site3.com</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Variable2</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">987</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">text4</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">www.site4.com</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana">Variable5</TD><TD style="FONT-SIZE: 10pt; FONT-FAMILY: Verdana; TEXT-ALIGN: right">555</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>=INDEX(Sheet1!$A$1:$G$5,MATCH(Sheet2!A2,Sheet1!$A$1:$A$5,0),MATCH(Sheet2!C2,Sheet1!$A$1:$G$1,0))</TD></TR><TR><TD>D3</TD><TD>=INDEX(Sheet1!$A$1:$G$5,MATCH(Sheet2!A3,Sheet1!$A$1:$A$5,0),MATCH(Sheet2!C3,Sheet1!$A$1:$G$1,0))</TD></TR><TR><TD>D4</TD><TD>=INDEX(Sheet1!$A$1:$G$5,MATCH(Sheet2!A4,Sheet1!$A$1:$A$5,0),MATCH(Sheet2!C4,Sheet1!$A$1:$G$1,0))</TD></TR><TR><TD>D5</TD><TD>=INDEX(Sheet1!$A$1:$G$5,MATCH(Sheet2!A5,Sheet1!$A$1:$A$5,0),MATCH(Sheet2!C5,Sheet1!$A$1:$G$1,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[doofusboy]

Since you are doing a VLOOKUP across worksheets, you'll need to use a named range.

First, assuming your data in Sheet1 is in the range A2:G5, highlight those cells and assign the range name "MyLookupRange".

Next, assuming your data in Sheet2 is in the range A2:C5, enter the following formula in cell D2 of Sheet2:

=VLOOKUP(A1,MyLookupRange,3,FALSE)

Last, copy formula down as needed.

 

[elchenuk]

Thanks for all your help guys, many ways to skin a cat! and they all worked perfectly, cheers!

eddy

 

[T. Valko]

Thanks for all your help guys, many ways to skin a cat! and they all worked perfectly, cheers!

eddy

You're welcome. Thanks for the feedback!