Your question is not clear. Can you be more specific please? It would also help if you provided a sample spreadsheet that indicates the problem and shows what you want the outcome to look like.

hi there,

sorry for beeing unprcise and thanks for the support. Please see it attached. basically if the looked up is twice in the search columns it would be great to get as an output both hits. Hoppe that clarifies in combination with the attached.

Have a lovely weekend.

Attached Files

compare countries.zip (60.8 KB, 17 views)

There are no Excel formulas that will do what you are asking. It may be possible using VBA, but for that you will need someone with programming skill. You could also do it using Microsoft Access, but that would mean changing a basic list into a database.

The simplest solution would be to rearrange your list so that there is only one country per row.

Cheers,

Hi there,
I have created a formula that I think solves your issue (see attachment). As you see, there has to be some formulas in column A and B to solve your issue (it is possible to move them further to the right if you like, but then you have to do a small modification).

In cell H2 there is a general formula, which can be copied down and right as far as you like.

I have created one "main solution", see sheet 2 (with correct error handling)
I have also to other alternatives, see sheet 3 and 4 (which creates "errors")

Kind regards
Bjorn

Attached Files

compare countries 2b.xls (99.0 KB, 13 views)

Hi again,
here is a slightly improved version. In this version, your list of countries doesn't have to be sorted.

Kind regards
Bjorn

Attached Files

compare countries 2c.xls (99.5 KB, 16 views)