CODEWITHSUNDEEP
excelvba
pokemon_Man
pokemon_Man

Reputation: 902

How to find a match in multiple array and array lookup?

I have some data that are in tables, i need to find a match in multiple array locations. If i'm looking for only one location i'm good, but if i need to find it in another location lets say, location!A6:J6 i get #value. Here my lookup.

=INDEX(location!A8:J8,MATCH(W_D!A6,location!A10:J10,0)) 'this is cell B6 formula *works fine

=INDEX(location!A6:J6&location!A8:J8,MATCH(W_D!A6,location!A4:J4&location!A10:J10,0)) 'Here is the issue.

Formula

Table

*Note: one of my table is upside down as well.

Upvotes: 1

Views: 3160

Answers (1)

A.S.H
A.S.H

Reputation: 29352

You can use a concatenated or a nested formula. There are many ways to compose it but may be the easiest would be to combine many INDEX/MATCH combinations so that the result would be the one that matches the entry.

I will first describe the general method and then apply it to your case. You have a search formula that works correctly in one range, but you want to apply many searches in many ranges. There are two methods to achieve this in general:

Method 1: Concatenation

=IFERROR(search1, "") & IFERROR(search2, "") & IFERROR(search3, "")

Only the successful search will appear as a result of this concatenation. If non succeeds, result is blank. However, if many succeed, result will be their concatenation.

Method 2: Nesting

We can also nest the IFERROR statements. In general this is more complex to edit but it solves the problem of duplicate results.

=IFERROR(search1, IFERROR(search2, IFERROR(search3, "Not Found")))

I prefer Method 1 in your case since you know that your search wont have duplicate results.So we keep it simple and apply the concatenation method to your case:

=IFERROR(INDEX(location!A6:J6,MATCH(W_D!A6,location!A4:J4,0)), "") &
 IFERROR(INDEX(location!A8:J8,MATCH(W_D!A6,location!A10:J10,0)), "")

With Method 2 the formula is:

=IFERROR(INDEX(location!A6:J6,MATCH(W_D!A6,location!A4:J4,0)), IFERROR(INDEX(location!A8:J8,MATCH(W_D!A6,location!A10:J10,0)), "Not Found")

Upvotes: 1

Related Questions