=INDEX(A:A,MATCH(C1,C:K,0)
This returns an error. How can I modify it so it tries to find C1 within the C:K range?
=INDEX(A:A,MATCH(C1,C:K,0)
This returns an error. How can I modify it so it tries to find C1 within the C:K range?
MATCH only looks in a 1-d array (i.e.one column) for a match. Please explain what you are trying to do.
Pete
If C1 is found in any of the cells in the same row between C:K then it would return the value in column A for the same row.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
If that formula did work the result would always be the value of cell A1.
You want to look in column C for a match of cell C1, so C1 will match C1.
Try this...
Data Range
A B C D E F G H I J K 1 ----- XXX Here 2 3 4 5 This aaa sss ddd fff eee rrr ttt yyy uuu 6 That dft oub lol ujk rrh qqq ppp ooo iii 7 The ww y rftg t qw oip ououu iklo ghqw 8 Other w kl dnjk uh ewe q w vbcx zsxcd 9 Here wwww j nmkl llllllll fghy XXX fh urf kpw 10 There fgty hbnj fgh jntu kl de dghhj tghhv drthj
This array formula** entered in D1:
=INDEX(A:A,MAX((C5:K10=C1)*ROW(C5:K10)))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
In the MAX function...
MAX((C5:K10=C1)*ROW(C5:K10))
...You should avoid using entire columns as range references. Use smaller specific ranges.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Why not find out where exactly the value is if it is in the range?
Formula:Please Login or Register to view this content.
....confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
If the value is found, the cell reference for the value is returned. If the value isn't in the range, Not Found is returned.
C D E F G H I J K 1n $E$6 2 3 4 5a g m s y ee kk ww 6b h n t z ff ll rr xx 7c i o u aa gg mm ss yy 8d j p v bb hh nn tt zz 9e k q w cc ii oo uu aaa 10f l r x dd jj pp vv bbb
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks