Nested IF Index Matches using 2 columns?

hengishammer

New Member
Joined
Feb 7, 2019
Messages
6
Office Version
  1. 365
Platform
  1. Windows
I have an index match formula that I'm not even sure that is possible to do.

This is what I wrote:
=(IF(V14>0,INDEX(CONTR_REF!J:J,MATCH(C14&H14,CONTR_REF!A:A,IF(W14>0,INDEX(CONTR_REF!K:K,MATCH(C14&H14,CONTR_REF!A:A,IF(W14&V14=BLANK,""))))))))

1636392265652.png


I want to index match a certain column if column V is greater than 0, and column W has nothing.
I want to index match a DIFFERENT column if column W is greater than 0, and column V has nothing.
I want nothing to happen if both Column V and W are populated
I want nothing to happen if both Columns V and W aren't populated.

Is it possible to write a single formula to display properly in column R?

I tried nested IFs but I think I researched myself into a margarita....
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
See if this works, needs to be array confirmed with Ctrl Shift Enter if using excel 2019 or older.
Excel Formula:
=IF(XOR(V14:W14>0),INDEX(CONTR_REF!J:K,MATCH(MATCH(C14&H14,CONTR_REF!A:A,0),1+(V14>W14)),"")
 
Upvote 0
Solution
Hey thanks for the reply! It didn't work though. I'm adding the row reference too--forgot that.

1636394100116.png


Note that in the original formula if V14 has something in it--it goes to grab column J:J in the index match.
If column W14 has something in it ---it goes to grab column K:K in the index match.

=(IF(V14>0,INDEX(CONTR_REF!J:J,MATCH(C14&H14,CONTR_REF!A:A,IF(W14>0,INDEX(CONTR_REF!K:K,MATCH(C14&H14,CONTR_REF!A:A,IF(W14&V14=BLANK,""))))))))

Same conditions as mentioned previously.

I really appreciate the collective's input.
 
Upvote 0
Hey wait a sec! I was able to fiddle with the formula and came up with this:

=IF(XOR(V14:W14>0),INDEX(CONTR_REF!J:K,MATCH(C14&H14,CONTR_REF!A:A,0),1+(V14<W14)),"")

This worked!

Awesome thanks a bunch for the help!!!!!(y)(y)(y)(y)(y)??????
 
Upvote 0
It didn't work though
Please be more specific when you say 'it didn't work'. Did it give an error? (#VALUE!, #N/A or similar). Did it give a result, but not that which was expected? Did excel reject the formula when you tried to enter it? Something else?

There was a lack of clarity in your post so some of it was guesswork based on your attempted formula.
 
Upvote 0
Yeah apologies for that. New here!

The formula said #N/A when pasted initially. (it wanted to add another end parentheses).

After that correction, I saw that there were 2 instances of "MATCH" in the formula that you listed. I removed 1 of them and then the formula worked, but was backwards in retrieving the desired lookup code due to the (V14>W14)),"") portion of the listed code. I switched out the greater than to say less than, and then the formula worked as intended.

You still helped me out big time though and I am eternally grateful:) Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top