Mapping Formula

[meppwc]

I am stuck with figuring out the correct formula for this situation. Your assistance is greatly appreciated.

Formula involves two worksheets named “MAIN” and “ORACLE”
Formula needed for cell W8327 of MAIN

Lookup value in S8327 of MAIN worksheet in column B of ORACLE worksheet
If found AND column BN of ORACLE equals “INV-MS SERVICE”, then map BH of MAIN to W8327 of MAIN.

If value not found in column B of ORACLE worksheet, then W8327 equals “MSN NOT FOUND”
If value is found in column B of ORACLE and value in BN does not equal “INV-MS SERVICE”, then W8327 equals “NOT ON SERVICE CONTRACT”

 

[KRice]

Lookup value in S8327 of MAIN worksheet in column B of ORACLE worksheet
If found AND column BN of ORACLE equals “INV-MS SERVICE”,

When searching column B of ORACLE, what is the largest number of matches that could be found? Could the S8327 value appear at most one time, or multiple times?
And if the S8327 value is found in column B of ORACLE, does your "if found AND..." statement mean to look at column BN of ORACLE on that same matching/corresponding row?

 

[KRice]

A small mock-up assuming the answers to my questions above are that only one match, at most, may occur in column B of ORACLE and if a match is found, you want the corresponding value from column BN on that same row in ORACLE.
Using this for ORACLE (I've inserted a leading "z" to make matches and non-matches for testing):

MrExcel_20240305.xlsx
	B																																																														BL	BM	BN
1	Col B Heading																																																																Col BN Heading
2
3	zluv123																																																																zINV-MS SERVICE
4
ORACLE

The MAIN sheet with some intermediate columns hidden:

MrExcel_20240305.xlsx
	S	T			W																																		BE			BH
8327	luv123				MSN NOT FOUND																																					colBHval
MAIN

Cell Formulas
Range	Formula
W8327	W8327	=LET(r,MATCH($S8327,ORACLE!$B:$B,0), IF(ISERROR(r),"MSN NOT FOUND",IF(INDEX(ORACLE!$BN:$BN,r)="INV-MS SERVICE",$BH8327,"NOT ON SERVICE CONTRACT")))

 

[meppwc]

The formula returned "MSN NOT FOUND", even though the value in main, "3100R300888", also appears on Oracle in cell B5, "3100R300888"

 

[KRice]

It works fine for me...at least based on the assumptions I mentioned. You'll need to post the formula you're using and answer the two questions I asked.

 

[meppwc]

The answer to both of your questions is yes. The value can only be found once. If found for example in cell B5, then over in BN5, I find the value of "INV-MS Service"

 

[KRice]

Okay, so the assumptions made by the formula are met. Could you do a direct copy and paste of the formula into another post?

 

[meppwc]

Not sure I follow.........you want me to start a brand new post of what I am needing?

 

[KRice]

No, just respond back with your formula, please.

 

[meppwc]

=LET(r,MATCH($S8327,ORACLE!$B:$B,0), IF(ISERROR(r),"MSN NOT FOUND",IF(INDEX(ORACLE!$BN:$BN,r)="INV-MS SERVICE",$BH8327,"NOT ON SERVICE CONTRACT")))