Lookup value question

[mattylefty26]

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

 

[etaf]

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

 

[mattylefty26]

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

 

[Aladin Akyurek]

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.

 

[etaf]

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

 

[Aladin Akyurek]

try using
=VLOOKUP(A2,Sheet2!A:E,5,FALSE)
[...]

You missed the point of my reply obviously...

 

[etaf]

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

 

[mattylefty26]

Thanks all! It was helpful. It works.