3 multiple conditions with multiple criteria formula help

Types of shippers:
Fedex
Purolator
Midland

Locations:
Ottawa
Vancouver
Montreal
Brampton

Accounts:
Purolator
Ottawa-456789
Vancouver-987452
Montreal-741258
Brampton-951357

Fedex
Ottawa-F789654
Vancouver-F456456
Montreal-F963147
Brampton-F654154




I need help creating a formula that will pretty much help with entering the above

ex. IF purolater is typed in cell A2 and Ottawa is typed in B2 then C2 will show 456789
IF Fedex it typed in A3 and in cell B3 has Ottawa again then C3 will show F789654

Hi and welcome to the forum

Im sure someone will come up with a shorter version, but until then...

If yoy create a small table, like this...

	A	B
24	Accounts:
25	Purolator	Fedex
26	Ottawa-456789	Ottawa-F789654
27	Vancouver-987452	Vancouver-F456456
28	Montreal-741258	Montreal-F963147
29	Brampton-951357	Brampton-F654154

Then in C2, copied down...
=RIGHT(INDEX($A$25:$B$29,MATCH(B2&"*",$A$25:$A$29,0),MATCH($A2,$A$25:$B$25,0)),LEN(INDEX($A$25:$B$29,MATCH(B2&"*",$A$25:$A$29,0),MATCH($A2,$A$25:$B$25,0)))-(FIND("-",INDEX($A$25:$B$29,MATCH(B2&"*",$A$25:$A$29,0),MATCH($A2,$A$25:$B$25,0)),1)))

Actually, if you can simplify the table to this...

	A	B	C
31		Purolator	Fedex
32	Ottawa	456789	F789654
33	Vancouver	987452	F456456
34	Montreal	741258	F963147
35	Brampton	951357	F654154

Then you san reduce that formula to this...
=INDEX($A$31:$C$35,MATCH($B2,$A$31:$A$35,0),MATCH($A2,$A$31:$C$31,0))

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)