Index and Match Formula gives #NUM! Error
Hi Guys!
I need some help with this formula which is very much needed for work. Struggling to find whats the problem with this formula. If you see my attached file, column A is where I put my Index and Match data but its gives #NUM! value. I just cant figure out whats the problem. Anyone's expertise and help is very much appreciated!
You can't multi-column INDEX whole columns like that. This version of your formula would work, in A2:
=INDEX(O:O, MATCH(B2&C2&D2, INDEX($R$1:$R$4000&$S$1:$S$4000&$M$1:$M$4000, 0), 0))
The problem is this is a LOT of calculations in one cell. As you copy that formula down I suspect your workbook will slow to a crawl.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Re: Index and Match Formula gives #NUM! Error
Hi!
Thank you..That was great help! Thank you so much.
I tried playing with that on my actual spreadsheet and it gave me the correct value for the
1st one and subsquents ones are all #NA! Now I dont know what went wrong again.
Appreciate your help again...Thanks so much..
You forgot some of the $ symbols. In D2:
=INDEX(W:W, MATCH(H2&I2&A2, INDEX($Z$1:$Z$1000&$AA$1:$AA$1000&$U$1:$U$1000, 0), 0))
Also, this is not an array formula, just press ENTER.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Re: Index and Match Formula gives #NUM! Error
Thank you! That helps very much. Appreciate it!
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks