You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I've got a bit out of my depth with this one, hopefully, it's easy for people who actually use Excel properly.
In spreadsheet 1 I need a formula that looks for the value in column E of the same row and then looks for that same value in column C of spreadsheet 2, returning the value that is in column F of that same row to the formula cell of spreadsheet 1.
Does that make sense?
Thanks
An example might help. Sounds like offset & match might do the job but hard to be sure.
So sheet 1...
Find value x in column E, (how are you determining which value sorry, just from a row number?)
Sheet 2
Find value x in column C return matching value in column F...
=vlookup(index(sheet1!e:e,A2),sheet2!C:F,4,false)
Where A2 is the row number, so A2=5 returns the value in E5, finds that in c and so on...
I knew I wasn't clear and I said sheet not workbook. Let me try again
Get the value that is in column E of same row as formula cell, find the same value in column C in a sheet of a different workbook and return the value that is column F of the same row
The value should only exist in once in each sheet of a workbook
From memory and not at a PC to test this...
Use Match function to find the row in column C then Index function to find the corresponding value in column F. So the formula would be something like =INDEX(column F reference, MATCH(Col E value, column C reference, 0))
The zero in last argument for Match function is needed to get an exact match
Rather than using column references it’s easier to use named ranges and makes formula much more readable (worth finding out about named ranges if you don’t know what they are).
Hope this works!!
In that case....
=vlookup(index(sheet1!e:e,row()),sheet2!C:F,4,false)
Finds the absolute row number of your formula, then the corresponding value in column E, finds that in column c of sheet2 and returns the corresponding result in column f.
Substitute the file location of the second workbook for Sheet2! (Easiest way is to open the second workbook and highlight the range you want when you get to the relevant part of the formula, so type =vlookup(index(sheet1!e:e,row()),
Highlight the range in workbook 2 type ",4,false"
Excel will then construct the link for you when you close workbook 2.
Bear in mind this will cease to work if you don't keep workbook 2 in the same place so it's no good for a second machine. For that, just copy the data from workbook 2 into a new sheet in workbook 1.
Vlookup is your friend - more reliable and less complex than Index and Match for what it sounds like you're asking.
I'm a muppet, no need for the index function at all
=vlookup(E1,sheet2!C:F,4,false)
Where e1 is the first cell ref.
Drag that down the length of your list and excel will auto increase e1 to e whatever
It was a bit silly asking this near the end of the day.
Thanks, I'll give the suggestions a go
It was a bit silly asking this near the end of the day.
Not really, i should have simplified my answer accordingly rather than just completing it when you made it clear you didn't want to pick a row "at random" but rather just to populate a list.
It worked, thanks very much
You're welcome