On Sheet Test I want a formula that looks at whatever is in G1 on Sheet Test and find this in Sheet 'Other' in E1:T20000, and return whatever is in col H where it is found, BUT there may be numerous instances of this word and therefore I only want it to return a result if sheet 'S' E15 matches sheet 'Other' col D on the row where the match is found.
Perhaps will need an Array formula for this..but:
1) As i remember you don't like Array formulas..Correct?
2) As i am too lazy for creating a sample worksheet can you pls upload yours?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Book1Sample.xlsx
Here you are....the formula should return the value 3.
Please try this file.
Please click 'Add reputation', if my answer helped you.
Thanks! Works fine!
You can also use this function along with Ctrl+Shift+Enter
=VLOOKUP(S!$E$15&Test!$G$1,CHOOSE({1,2},Other!$D$1:$D$6&Other!$E$1:$E$6,Other!$H$1:$H$6),2,0)
..also if you like take a look here.
http://www.excelforum.com/the-water-...ernatives.html
martindwilson in his attachment describe a non array solution for this!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks