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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,389
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

New Member
Joined
Sep 12, 2014
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

Well-known Member
Joined
Oct 24, 2012
Messages
3,389
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:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,389
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
 

Forum statistics

Threads
1,081,726
Messages
5,360,903
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top