Return a vales when matched to a range on another sheet

J

JPHenning

Board Regular
Joined
Jun 5, 2008
Messages
189
Hi,
I am having problems with a formula can anyone help.
I locate and display a value an a worksheet when it is matched from a value of another sheet.

The Formula I think I need is Index and Match but I cannot get it to display the value I require,

On the sheet I want the formula I have a month and year Value in cell B1.
On the second sheet I have a range B5:Y5 that are the Months Jan-09 through to Dec-09 but with a blank column next to it for % further down the sheet.

I need to match the month in cell B1 to the correct month in the range on sheet2 then put the value that corosponds with net sales in cell D8 on sheet1

so it is Cell D8 i need the formula I thought it may be
=INDEX('Sheet2'!B5:Y59,MATCH(B1,'Sheet2'!B5:Y5))

This is sheet1<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
sorry Sheet 2​
<CENTER> </CENTER><CENTER>http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE< a>cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="9" bgColor="#0c266b"><TABLE border=0 width="100%" align=center><TBODY><TR><TD align=left>Microsoft Excel - P&L New V2.xls</TD><TD style="FONT-FAMILY: caption; COLOR: #ffffff; FONT-SIZE: 9pt" align=right>___Running: 11.0 : OS = </TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px; BORDER-RIGHT: #000000 0.5pt solid" colSpan="9" bgColor="#d4d0c8"><TABLE border=0 width="100%" align=center VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-FAMILY: caption; COLOR: #000000; FONT-SIZE: 10pt">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</TD><TD vAlign=center align=right><FORM name=formCb059465><INPUT onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' value="Copy Formula" type=button name=btCb290334></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="9" bgColor="white"><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD style="WIDTH: 60px" bgColor=white align=middle><SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION selected value="='B967'!B6+'B1119'!B6">B6<OPTION value="='B967'!D6+'B1119'!D6">D6<OPTION value="='B967'!F6+'B1119'!F6">F6<OPTION value="='B967'!H6+'B1119'!H6">H6<OPTION value="='B967'!B7+'B1119'!B7">B7<OPTION value==B7/$B$6>C7<OPTION value="='B967'!D7+'B1119'!D7">D7<OPTION value==D7/$B$6>E7<OPTION value="='B967'!F7+'B1119'!F7">F7<OPTION value==F7/$B$6>G7<OPTION value="='B967'!H7+'B1119'!H7">H7</OPTION></SELECT></TD><TD bgColor=#d4d0c8 width="3%" align=right>=</TD><TD bgColor=white align=left><INPUT value="='B967'!B6+'B1119'!B6" size=80 name=txbFb965317></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" width="2%" align="middle">
</TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>A</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>B</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>C</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>D</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>E</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>F</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>G</CENTER></TD><TD style="BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align="middle"><CENTER>H</CENTER></TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align="middle"><CENTER>5</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #0000ff; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Jan-09</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #ff0000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">%</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #0000ff; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Feb-09</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #ff0000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">%</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #0000ff; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Mar-09</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #ff0000; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">%</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #0000ff; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-TOP: #000000 0.5pt solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 0.5pt solid">Apr-09</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align="middle"><CENTER>6</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #0000ff; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Net Sales</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">290,316</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">282,919</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">308,403</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid"> </TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">325,878</TD></TR><TR><TD style="BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; FONT-FAMILY: menu; COLOR: #000000; FONT-SIZE: 10pt; BORDER-TOP: #000000 0.5pt solid" width="2%" align="middle"><CENTER>7</CENTER></TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #0000ff; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; FONT-WEIGHT: bold; BORDER-RIGHT: #d4d0c8 0.5pt solid">Food Cost</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">84,729</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">29.19%</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">81,486</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">28.07%</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">88,290</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #d4d0c8 0.5pt solid">30.41%</TD><TD style="BORDER-BOTTOM: #d4d0c8 0.5pt solid; TEXT-ALIGN: right; BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Times New Roman; COLOR: #000000; FONT-SIZE: 8pt; VERTICAL-ALIGN: bottom; BORDER-RIGHT: #000000 0.5pt solid">92,789</TD></TR><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" colSpan="9"><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-BOTTOM: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff; WIDTH: 120pt; BORDER-TOP: #808080 0.5pt solid; BORDER-RIGHT: #000000 0.5pt solid" align=left>BMark's Group</TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</CENTER></FORM>
</CENTER>
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Match dynamic value in another sheet and return values in that column as a drop down list
Replies
3
Views
371
duggie33
D
SaraWitch
  • Question
Highlight cell if text matches in range of cells, but only if other cells are blank
2
Replies
11
Views
309
SaraWitch
SaraWitch
C
  • Question
Problem with Match function when lookup data is in another sheet
Replies
2
Views
200
Fluff
Fluff
B
  • Solved
partial text lookup /match in a table and return value with date range and return the month sum value
2
Replies
10
Views
736
Sufiyan97
Sufiyan97
A
  • Question
Return value from array range on another sheet dynamically
Replies
1
Views
456
ExceLoki
ExceLoki

Forum statistics

Threads
1,214,381
Messages
6,119,192
Members
448,874
Latest member
Lancelots

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