How to nest INDEX and Match into IF statements?

**neojihui** · 08-18-2015, 12:50 AM

Hi all,

I have been reading through a couple of threads but cannot seem to wrap my head around adding IF to Index and Matching statements.

What I want to do is to try to create a formula for the following,

1. For all entries for x patient under CURRENT DATA, I want to look up the closest absolute value to 25% (under %max time)
2. Then input the corresponding data in the same row into the cells on the NEW DATA side.

For Example:

file.png

Looking at this, for Patient 2, closest value will be 27%, and the values in the other cells will be 2%, 92, 90 and 1 respectively.

The if, match and index formula that I have always returns a false value.

=IF(A3:A526=H5,(INDEX(C:C,MATCH(MIN(ABS(C:C-N1)),ABS(C:C-N1),0))),1)

This is the formula I have tried. Could anyone help me out with this?

Thank you.

Regards
Matthew

**FDibbins** · 08-18-2015, 12:55 AM

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

**neojihui** · 08-18-2015, 01:01 AM

Hi Ford,

Thanks! Sorry about that. I didn't think of it.

I've uploaded the sample!

Cheers
Matthew

**oeldere** · 08-18-2015, 03:04 AM

I used helpcells to get the result.

See the yellow cells for the helpcells.

See the green cells for the result.

I used index / match after I added column A and C together in the current data.

P.s. you don't added what value should be showing if the differance in absolut way is the same.

**skywriter** · 08-18-2015, 03:42 AM

Hi Matthew,

I'm not a real formula guy, but I managed to come up with a solution. The efficiency of these formulas is probably low, but it was fun working them out. oeldere's are probably more efficient with the helper columns, but here you are.

I ran these on your test sheet. They were written in cells I2:M2.
I2, K2,L2 and M2 are all array formulas which means they must be entered with Control + Shift + Enter, not just Enter.
The formula in J2 is just a regular formula, use Enter.

I2:

Formula:

Please Login or Register  to view this content.

J2:

Formula:

Please Login or Register  to view this content.

K2:
This formula can be copied into L2 and M2.

Formula:

Please Login or Register  to view this content.

Good Luck.

**neojihui** · 08-20-2015, 05:10 AM

Thanks everyone, I'll give these a go and hopefully it turns out alright!

How to nest INDEX and Match into IF statements?

LinkBack

Thread Tools

Rate This Thread

Display

How to nest INDEX and Match into IF statements?

Re: How to nest INDEX and Match into IF statements?

Re: How to nest INDEX and Match into IF statements?

Re: How to nest INDEX and Match into IF statements?

Re: How to nest INDEX and Match into IF statements?

Re: How to nest INDEX and Match into IF statements?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Nested If Statements or Match/Index???

Multiple If statements or INDEX MATCH? Im confused.

[SOLVED] IF statements with nested INDEX MATCH

HOW TO ? - Having issue with INDEX / Match and to nest with IF

Using INDEX/MATCH With Multiple IF Statements

Index Match - nested If (less than/greater than statements)

index/match lookup - with If statements

Bookmarks

Bookmarks

Posting Permissions