# Index Match, search 1 criteria across multiple columns

1. ## Index Match, search 1 criteria across multiple columns

Hey guys, Im having trouble setting up an index match that searches 1 value across multiple columns. Attaching an example spreadsheet.
Thanks for the help

Column A Column B Column C
a e 1
b f 2
c g 3
d h 4

Basically want to do an index match that searches any letter from column A&B and returns the number on C. I tried =INDEX(C2:C5,MATCH(F2,A2:B5,0)) where F2 has the desired letter

2. ## Re: Index Match, search 1 criteria across multiple columns

Hi,

One way

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Index Match, search 1 criteria across multiple columns

Hi izk,

Put this in G2

=INDEX(\$C\$2:\$C\$5,IFERROR(MATCH(F2,\$A\$2:\$A\$5,0),0)+IFERROR(MATCH(F2,\$B\$2:\$B\$5,0),0))

4. ## Re: Index Match, search 1 criteria across multiple columns

This works great for this example but when I have to search for a value within 60 columns the formula would be impossibly long to write. any suggestions?

5. ## Re: Index Match, search 1 criteria across multiple columns

Try this...

=SUMPRODUCT((A2:B5=F2)*C2:C5)

6. ## Re: Index Match, search 1 criteria across multiple columns

ok izk,

If you have more columns the problem now gets better. Try the formula in G2 after I've added some more columns and put in both number and text into the array.

Array Lookup to Index from correct row..xlsx

7. ## Re: Index Match, search 1 criteria across multiple columns

Tony, your solution only works if there is no numbers in the array only letters such was my example, but I actually need it to work with both numbers and letters.
MarvinP, your solution works great with another limitation I have to semicolon each row, in my case I have more than 2000 rows and more than 60 columns. So going 1;2;3;4;5, all the way to 3000 is not the best idea. Any other solutions?

8. ## Re: Index Match, search 1 criteria across multiple columns

Try this. It will work with numbers and text (they must be unique in the source data).
Formula:
`Please Login or Register  to view this content.`

Edit This should shrink and grow with the number of columns, but the range will still need to be manually input.

9. ## Re: Index Match, search 1 criteria across multiple columns

Withdrawn by FR. Double posted.

10. ## Re: Index Match, search 1 criteria across multiple columns

I am really sorry but it is still not working when I plug it into my large data. I found it easier to just attach the actaul workbook I need to to work in. Here it is (Book1)

I highlighted in yellow the column where I am typing the formula on sheet2 hope you can help.

thanks again

11. ## Re: Index Match, search 1 criteria across multiple columns

Thank you izk630 for the upload.

None of the examples you provided until now had concatenated strings containing the lookup values.

This is a different problem. There is evidence of multiple matches. I am not surprised my formula did not work.

12. ## Re: Index Match, search 1 criteria across multiple columns

any ideas how to solve this? index match or vlookups work fine with repeated values, it just returns the first found or max/min if specified.

13. ## Re: Index Match, search 1 criteria across multiple columns

Hey izk,

You don't need the {1;2;3;4} as you can use a vertical range of those numbers instead. See the attached where I've done a range pointing to a vertical range in the formula. You could make a vertical column of 3000 numbers and the formula would work.
Array Lookup to Index from correct row Better..xlsx

14. ## Re: Index Match, search 1 criteria across multiple columns

MarvinP,
When pluggin in your solution to my larger model it returns the wrong result. It just returns the same values as my index in the same order. I indexed line numbers in the example so you can see.

15. ## Re: Index Match, search 1 criteria across multiple columns

MarvinP.
I figured out that when a value not in the sumproduct range is typed in, you get the first result, then the second and consecutively. Any way for the formula to give me an NA if value not found?

16. ## Re: Index Match, search 1 criteria across multiple columns

I missed a detail. Those numbers are already parsed in N:AV. My apologies.

In the meantime I reworked my formula in the upload Post #10. This must be array entered. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
Formula:
`Please Login or Register  to view this content.`
This returns dates, and I don't think it's doing what you want. It matches only the first 'pono' (column A) that it finds. In column W of Sheet2 I array entered this segment of that formula and summed it. There are as many as 16 occurrences of 'pono' numbers.
Formula:
`Please Login or Register  to view this content.`
Did you want the multiple outputs? In the columns to the right of 'result'?

17. ## Re: Index Match, search 1 criteria across multiple columns

Hi izk,

After studying your last attached, could you show some examples of what belongs in your Sheet2 yellow cells. Also could you give an overview of what the problem really is. Something like, "We ship products out and get error back". Sheet 1 is out and Sheet2 is back. We need to see who supplied the shipment.....

When these problems get this hard, I sometimes find a small adjustment to the tables or using relationship between the tables to get a better answer than what we have suggested above.

18. ## Re: Index Match, search 1 criteria across multiple columns

this si great thank you so much!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1