Matt Pinkston
asked on
MS Excel Multi Sheet Formula
I have a workbook with two tabs
main & RAPT
I need a formula for a column in main that will look at column A and see if it has a -NG in it, if it find an -NG then it should use what it found plus 7 so an example would be -NG1595. Then look in column B of Sheet RAPT and if that -NG#### is found anywhere in that column (could be part of a string) then bring column Q from RAPT across..
main & RAPT
I need a formula for a column in main that will look at column A and see if it has a -NG in it, if it find an -NG then it should use what it found plus 7 so an example would be -NG1595. Then look in column B of Sheet RAPT and if that -NG#### is found anywhere in that column (could be part of a string) then bring column Q from RAPT across..
ASKER
so an example of A2 in Main would be
PMNMCI-5000-2015-NG1595 Acronis
and an example of A2 in RAPT could be
NG1595
So we would look for -NG1595 less the (-) in column A of Rapt and when there is a match I want column Q to be grabbed
PMNMCI-5000-2015-NG1595 Acronis
and an example of A2 in RAPT could be
NG1595
So we would look for -NG1595 less the (-) in column A of Rapt and when there is a match I want column Q to be grabbed
Then try below:
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP("NG"&"*",RAPT!B:Q,16,0),""),"")
If NG**** position will be same after PMNMCI-5000-2015- then we can use below formula:
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,18,6),RAPT!B:Q,16,0),""),"")
If position is not fixed but NG letters would be 6 digit then try below:
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!B:Q,16,0),""),"")
ASKER
HMMMM Results are very weird...
Formula returned a lot of blanks and a bunch of 1/0/1900
Details again...
Two Tabs
Tab1 - MAIN
Column A staring in Row 2 has names that look like this:
PMNMCI-5000-2015-NG1595 Acronis
PMNMCI-5000-2015-NG1424 HW and SW Certification and Accreditation
PMNMCI-5000-2014-NG0225-MO D12 Mobility Consolidation
So need to extract the NG#### and then look in tab RAPT column A which looks like this
NG3498
NG3497
NG3496
NG3495
NG3494
If a match is found the value from column Q should be used...
Formula returned a lot of blanks and a bunch of 1/0/1900
Details again...
Two Tabs
Tab1 - MAIN
Column A staring in Row 2 has names that look like this:
PMNMCI-5000-2015-NG1595 Acronis
PMNMCI-5000-2015-NG1424 HW and SW Certification and Accreditation
PMNMCI-5000-2014-NG0225-MO
So need to extract the NG#### and then look in tab RAPT column A which looks like this
NG3498
NG3497
NG3496
NG3495
NG3494
If a match is found the value from column Q should be used...
Matt,
Please find attached with below formula:
Please find attached with below formula:
=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!B:Q,16,0),""),"")
Matt-Partial-Vlookup.xlsx
ASKER
Using your formula did not give me the results I was looking for..
MAIN Column(A) = PMNMCI-5000-2015-NG1595 Acronis
RAPT Column(A line 1696) = NG1593
RAPT Column(Q) = 9/30/2015 11:55
But what gets returned is BLANK
If I change the formula to
=IF(ISNUMBER(SEARCH("NG",A 2)),IFERRO R(VLOOKUP( MID(A2,FIN D("NG",A2) ,6),RAPT!A:Q,16,0),""),"")
I get 1/0/1900
MAIN Column(A) = PMNMCI-5000-2015-NG1595 Acronis
RAPT Column(A line 1696) = NG1593
RAPT Column(Q) = 9/30/2015 11:55
But what gets returned is BLANK
If I change the formula to
=IF(ISNUMBER(SEARCH("NG",A
I get 1/0/1900
If something returns 1/0/1900 then you need to format cell as General
Matt, see your initial post you asked be
What is the expected result, is it a number, currency or date?
Then look in column B of Sheet RAPT
What is the expected result, is it a number, currency or date?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome!!!!
You're Welcome Matt, Glad I was able to help.
Is this duplicate question? https://www.experts-exchange.com/questions/29009085/MS-Excel-Formula-Help.html
If yes, then please request it for deletion, else accept the solution there as well.
Is this duplicate question? https://www.experts-exchange.com/questions/29009085/MS-Excel-Formula-Help.html
If yes, then please request it for deletion, else accept the solution there as well.
I am still not sure about plus 7, I am assuming you want see If Col A has -NG then plus 7 Col, which would be Col H, then you want to lookup combination of A & H in RAPT Sheet in Col B to extract Col Q.
If this is the case, then please try below formula.
Open in new window