 # 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..
Microsoft ExcelMicrosoft Office Last Comment
Shums Faruk

8/22/2022 - Mon
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
Shums Faruk

Then try below:
``````=IF(ISNUMBER(SEARCH("NG",A2)),IFERROR(VLOOKUP("NG"&"*",RAPT!B:Q,16,0),""),"")
``````
Shums Faruk

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

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

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

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
Matt Pinkston

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

If something returns 1/0/1900 then you need to format cell as General
Shums Faruk

Then look in column B of Sheet RAPT

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Matt Pinkston