Lookup value question

mattylefty26

New Member
Joined
Sep 12, 2014
Messages
3
Hello, I have a formula I'm creating in Excel file #1. It takes the value in the same position in Excel file #2 and divides by a value. It then takes a value from another sheet within file #1 and computes with other values.

1. If I reference file #2 in file #1 and both files are copied and pasted into a new folder, the formula loses the path embedded within. Can I create the equation so it just looks in the same directory/folder so if the user changes both files to another location, it will still be functional - as long as the files are in the same folder and not separated?

2. How do I select the value in another sheet based on these parameters: A1 text string is '2 inch'. I need to call a certain value from another sheet located in Column C and row containing text string '2 inch' and place it in B1.

Any assistance is greatly appreciated.
Matt
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
1. If I reference file #2 in file #1 and both files are copied and pasted into a new folder, the formula loses the path embedded within. Can I create the equation so it just looks in the same directory/folder so if the user changes both files to another location, it will still be functional - as long as the files are in the same folder and not separated?
how are you refering
dont use the path - just the excel workbook name

for 2) Vlookup may help - but can you provide more info
or a sample sheets- maybe on a share like onedrive/dropbox
 
Upvote 0
I've attached a link to a sample excel spreadsheet. A user will input rows of data in any order (column A & B), and Column C calculates a parameter from Column B and the lookup table in the next sheet.

2. How do I select the value in another sheet based on these parameters: A2 text string is '2 inch'. I need to call a certain value from another sheet located in Column C and row containing text string '1 inch' (E3 in this example) and place it in C4.

https://db.tt/immCp40U

Thank you in advance with any assistance.
Matt
 
Upvote 0
I've attached a link to a sample excel spreadsheet. A user will input rows of data in any order (column A & B), and Column C calculates a parameter from Column B and the lookup table in the next sheet.

2. How do I select the value in another sheet based on these parameters: A2 text string is '2 inch'. I need to call a certain value from another sheet located in Column C and row containing text string '1 inch' (E3 in this example) and place it in C4.

https://db.tt/immCp40U

Thank you in advance with any assistance.
Matt

Better post your sample here, so that it's available here for perusal in a way that a download is not necessary.

You seem looking for something like:

=VLOOKUP(A2,Sheet2!A:B,2,0)

which fetches a value that corresponds to A2 in a 2-column table on Sheet2.
 
Upvote 0
try using
=VLOOKUP(A2,Sheet2!A:E,5,FALSE)

you cannot attach files to the forum, you would need to use a special add-in here to show excel in the forum "jeanie"

see attached here in dropbox

https://www.dropbox.com/s/hdxtnv04lomgckh/example_vlook_etaf.xlsx?dl=0

the #N/A is because 4" is not listed - we can change the formula to

=IFERROR( VLOOKUP(A2,Sheet2!A:E,5,FALSE) , "" )

and that will leave the cell blank if it cannot find the matching value

the lookup looks for the value in column A on sheet 2 and then returns the value in the 5th column
=IFERROR( VLOOKUP(A2,Sheet2!A:E,5,FALSE) , "" )

this part of the formula
=IFERROR( VLOOKUP(A2,Sheet2!A:E,5,FALSE) , "" )
tells excel to look down column A
and the range then allows you to specify the column - ie if the range was A:C you could only use 3 and return upto the 3rd column
the range must include or exceed the column you want to return the information from
 
Last edited:
Upvote 0
You missed the point of my reply obviously...
NO, i did not miss the point , I just used the example the OP provided and gave the exact syntax and explained why
 
Upvote 0

Forum statistics

Threads
1,223,287
Messages
6,171,201
Members
452,390
Latest member
kemafa77

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