Forum menu
brain frazzled, need help
I wnat to search a range of text filled cells to return a value from that row if all the cells in the 'lookup value' range = all the cells in the range in the equivalent doc.
Basically I want a way of copying the text in a cell and pasting it into the reciving cell if all the preceeding values in the recieving row match the source row.
The backstory is I've compiled a 'master register' based on the content of a number of 'account registers' but have made some chages/additions and now want to 'back populate' the account registers with this new information............
GO!
email me a sample workbook and I *may* be able to help 🙂
What do you mean by 'preceding cells'.
Above the cell whose value you want to return? to the left of it?
You can do a vlookup where the lookup value is concatenated e.g
=VLOOKUP(G2&H2,A:C,3,FALSE)
but the first column of the range has to be one cell (e.g. G2 is "single" and H2 is "track" so it will only find a match if a cell in column A contains "singletrack"
I'd make a big concatenated cell that picks up everything relating to that line e.g.:
=A2&"¦"&B2&"¦"&C2&"¦"&D2 etc
and do the same in the destination sheet, then do the lookup or match & index between those two cells rather than trying to do it on a range.
I'd make a big concatenated cell that picks up everything relating to that line e.g.:
=A2&"¦"&B2&"¦"&C2&"¦"&D2 etc
would work.
Also, SUMPRODUCT, would be another way.
Rob's obviously bored this afternoon, so email him. I have to go cook tea for the [s]animals[/s] children 🙂
SUMPRODUCT
It's like he can read my mind 🙂
Legend. The madness is over. Thanks again.
Can you explain how sumproduct would be used in this case?
I'd have gone down the concat route so interested in learning alternative techniques.
Wow, I was going to post up some bullshit about concatenating. Never been "right" before
I'm claiming sub-guru status 😀
dufusdip - it's really sad, but I got all excited about mastering a SUMPRODUCT technique a while ago and stuck it up in here
http://singletrackworld.com/forum/topic/just-learnt-a-powerful-new-excel-technique
Rob is proper smartz at this and added some very useful additional stuff to.