Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-09-2016, 07:45 AM
mcronin mcronin is offline Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet Windows 7 64bit Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet Office 2016
Novice
Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet
 
Join Date: May 2016
Posts: 4
mcronin is on a distinguished road
Default Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet

Hi,



I have a sample file that is attached to this post that is basically a scaled down version of what I am trying to do.

I am looking to return a price from sheet 1(pricing table) into a column("What it should be") in sheet 2(raw data) given criteria in the raw data. Line 1 of the raw data shows that we need to return the price for Product FRO for JJJ company shipped by JR's and obviously I would want this formula or macro to return the correct value for each line not just the one.

My only idea so far is making a really long IF statement formula, but when I use this on my real data that won't work there are just too many variables. I am sure there is a much easier and far more efficient way of doing this.

Please let me know if more info is needed to help answer this question.

Thanks in advance!
Attached Files
File Type: xls Sample File(Egus fee test).xls (26.5 KB, 7 views)
Reply With Quote
  #2  
Old 05-09-2016, 09:43 AM
jeffreybrown jeffreybrown is offline Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet Windows Vista Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet Office 2007
Expert
 
Join Date: Apr 2016
Posts: 645
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

In G2 copied down...
Code:
=INDEX(Sheet1!$G$3:$J$7,
          MATCH($B2&$E2,Sheet1!$C$3:$C$7&Sheet1!$A$3:$A$7,0),
          MATCH($A2,Sheet1!G$1:J$1,0))
IMPORTANT
  • This is an array formula
  • Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
  • If entered correctly, the formula will be enclosed in {brackets}
  • Do not enter the {brackets} manually
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet Can a Data Source be one sheet in a multi-sheet Workbook nfotx Mail Merge 1 07-01-2015 12:55 AM
Move data from one sheet to the next available row or cell of another sheet bwwalker08 Excel 0 04-10-2015 11:51 AM
Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet How to return a value in a data table using vertical and horizontal criteria oswald Excel 1 02-08-2015 02:01 AM
Populate sheet 3 with data from sheet 1 and sheet 2 speck Excel Programming 0 01-14-2015 07:54 AM
Want to return a value from my pricing table into my raw data sheet given criteria in the data sheet Help Coloring a cell in Sheet one if data is missing from another sheet Aeducan Excel 1 06-22-2014 04:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft