# 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..
Shums Faruk

Hi Matt,

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.
``=IF(LEFT(A2,3)="-NG",IFERROR(VLOOKUP(LEFT(A2,3)&H2,RAPT!B:Q,16,0),""),"")``
Matt Pinkston

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
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),""),"")``

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-MOD12 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...
Matt,

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

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",A2)),IFERROR(VLOOKUP(MID(A2,FIND("NG",A2),6),RAPT!A:Q,16,0),""),"")

I get 1/0/1900
If something returns 1/0/1900 then you need to format cell as General
Then look in column B of Sheet RAPT

What is the expected result, is it a number, currency or date?
Shums Faruk

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.