Thread: Index Match with IF

Hi,

see attached file..

i cant seem to figure out why the formula is not working..

with only reference (one column) it works.. but not with crosschecking with two columns..
i even tried with added vlookup..

Could you please check the formula what went wrong?

Its about multiple way matching..
if city matches, then if company matches = Col C Number
if city matches, then company doesnt match but sub company matches = Col D Number
and so on
i also plan to add 2 more columns like that..


thanks

try

=IFERROR(IF(MATCH(D4,ACCOUNT!$A$2:$A$6,0)=MATCH(E4,ACCOUNT!$B$2:$B$6),C4,IF(MATCH(DATA!E4,ACCOUNT!$B $2:$B$6,0)>0,D4,"")),"")

Hello,

You can use just one formula if you fix several problems:

Sub Company in both sheets needs to be exactly the same text (on the Account sheet, there is a line break after Sub)
Use relative and absolute referencing correctly (your formula had some relative references that change when the formula is copied down).

Starting in row 4, this formula will extract the correct account number

=INDEX(ACCOUNT!$A$2:$D$6,MATCH(DATA!E4,ACCOUNT!$B$2:$B$6,0),MATCH(DATA!F4,ACCOUNT!$A$1:$D$1,0))

cheers,

JieJenn, did you test that? It does not return a single valid result in the spreadsheet, for obvious reasons, of course.

JieJenn, if you don't take the time to test, that's one thing. But editing your post after it has received replies will distort the flow of the discussion. Please don't do that again. Stand by your posts.

I have restored your original post. Maybe you want to take some time for QA before posting to avoid such situations.

thanks,

to illustrate, here's the file with the corrected text on the Accounts sheet and the formula in action.