Formula needed that can find a value from a list and then check another cell for a certain letters?

[danbates]

Hi,

I asked this question last year and I was given a solution to it but unfortunately now the previous solution is now no longer valid.

Here is my previous thread:

Formula needed that can find a value from a list and then check another cell for a certain letters?

Hi, Please can someone help me? I have the following table: XQ 168 GB 208 XR 208 XI 144 DE 192 In column 3 I'd normally use vlookup but it's giving me the wrong number. Here is my table I am looking from: 208 - GB 1 144 2 208 - DS 3 168 4 192 5 Using vlookup in...

www.mrexcel.com

Now I need the formula to do the same but also be able to work the opposite way around. So if the 234 is above the 234 - GB (post 7)

Any questions then please ask and any help would be much appreciated.

Thanks

Dan

 

[Alex Blakenburg]

Please provide an example of your data and the expected result.
Typically you would try to look for the more precise value first, in your case being code & " - " & country and if it can't find it just look for code.
I think that is what the formula you have does.
(Post #4 - =IFERROR(LOOKUP(2,1/FIND(B1&" - "&A1,A$9:A$13),B$9:B$13),LOOKUP(2,1/FIND(B1,A$9:A$13),B$9:B$13)) )

What is it that you want differently ? If you look for just the code first it will only look for the combined option if the code on its own doesn't exist in your table.

 

[Alex Blakenburg]

The only thing that concerns me a little is the use of find (wildcard search) when just looking up the code, so in case that is your issue try the below.

20221025 Lookup Multi Criteria and alternate danbates.xlsm
	A	B	C	D	E	F	G	H	I
1	Data --->		Code	Cntry	Result		Lookup -->	Code	Cntry
2			234	GB	8			126	1
3			208	XL	5			63	2
4			144	CN	6			216	3
5			192	GB	9			208 - GB	4
6			234	MX	7			208	5
7			208	GB	4			144	6
8			234	GB	8			234	7
9			208	XR	5			234 - GB	8
10			126	IL	1			192	9
11			234	IL	7
12			208	GB	4
13			234	FR	7
Sheet1

Cell Formulas
Range	Formula
E2:E13	E2	=IFERROR(IFERROR(LOOKUP(2,1/(($H$2:$H$10)=(C2&" - "&D2)),$I$2:$I$10),VLOOKUP(C2,$H$2:$I$10,2,FALSE)),"Not Found")