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
Hi,
One way
Formula:
=IFERROR(IFERROR(MATCH(F2,A2:A5,FALSE),MATCH(F2,B2:B5,FALSE)),"")
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
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))
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
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?
Try this...
=SUMPRODUCT((A2:B5=F2)*C2:C5)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
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
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?
Withdrawn by FR. Double posted.
Dave
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
Try this. It will work with numbers and text (they must be unique in the source data).Formula:=INDEX(C2:C5,MATCH(1,MMULT(--(A2:B20000=F2),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(A2:B20000)))^0),0))
Edit This should shrink and grow with the number of columns, but the range will still need to be manually input.
Last edited by FlameRetired; 10-20-2016 at 10:54 PM.
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.
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.
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
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.
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?
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.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:=IFERROR(INDEX(webtracker!$H$1:$H$1001,MATCH(1,MMULT(IF(ISNUMBER(FIND(A2,webtracker!$N$1:$AV$1001)),1,0),ROW(INDEX(webtracker!A:A,1):INDEX(webtracker!A:A,COLUMNS(webtracker!$N$1:$AV$1001)))^0),0)),"")Did you want the multiple outputs? In the columns to the right of 'result'?Formula:=SUM(MMULT(IF(ISNUMBER(FIND(A2,webtracker!$N$1:$AV$1001)),1,0),ROW(INDEX(webtracker!A:A,1):INDEX(webtracker!A:A,COLUMNS(webtracker!$N$1:$AV$1001)))^0))
this si great thank you so much!!!
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks