Help with index and comparison

[excel_excel_excel]

Hi,

I think I`m onto something, but I can`t finish it. I been struggeling with this for some hours now.
I apprechiate help alot.

Please download the file and check it out.

[MarvinP]

Hi Exces*3

Match() only works on a single column or row. See if the attached gets what you want. I don't know if you type in the AA, BB, CC or it needs to be calculated from the number. Please describe what is given (typed in) and what needs to be formulated.

Index Match Multiple Columns.xlsx

[ChemistB]

From what you've described, I'd try this

Please Login or Register  to view this content.

The second INDEX/MATCH finds the right column to searched for your number, then the first finds your Santa

[excel_excel_excel]

Hi thank you so much, It dosen`t work very well.

I`ve uploaded a new file. I think it explains better what I`m trying to do. There are two examples as you can see.

Thank you for helping me, I`m very grateful.

Index Match Multiple Columns_v2.xlsx

Screenshot_excel.JPG

[congnt92]

Try this ARRAY formula

Please Login or Register  to view this content.

[Bo_Ry]

Maybe Try at B6

=INDEX(E4:E8,MATCH(ROUND(C4,1-MATCH(B4,F3:H3,)),INDEX(F4:H8,,MATCH(B4,F3:H3,)),))
or
=INDEX(E:E,MAX(INDEX(ROW(E4:E8)*(MIN(INDEX(ABS(F4:H8-C4),))=ABS(F4:H8-C4)),)))

[congnt92]

@Bo_Ry
I don't think that Round() and Match(,,false) can work together well since round() changes the lookup value while match(,,false) is the exact match.

[Bo_Ry]

@congnt92 Thanks.
In most of the case it won't work, but this one work because it round to match lookup array Eg; 15 round to 20 and 150 round to 200

But still, we better use aproximate match by remove last comma.
=INDEX(E4:E8,MATCH(ROUND(C4,1-MATCH(B4,F3:H3,)),INDEX(F4:H8,,MATCH(B4,F3:H3,))))

[excel_excel_excel]

Now I`m so close...
Check out the new file. Thank you very much, couldent do it without the help from you guys!

Almost there....xlsx

[Bo_Ry]

Maybe

=INDEX(B:B,MOD(MIN(INDEX(ABS(INDEX(C3:I19,,MATCH(K2,C2:I2,))-L2)*10^6+ROW(B3:B19),)),10^6))

Santa 6 355 is the closest to 350

[congnt92]

@Bo_Ry
Yep, it works with sample data (post #1)
But it's not a "safe" formula.
=INDEX(E4:E8,MATCH(ROUND(C4,1-MATCH(B4,F3:H3,)),INDEX(F4:H8,,MATCH(B4,F3:H3,)),))
will not work if G4=10.1
=INDEX(E:E,MAX(INDEX(ROW(E4:E8)*(MIN(INDEX(ABS(F4:H8-C4),))=ABS(F4:H8-C4)),)))
will not work if H5=10.05

[congnt92]

@excel*3
Try

Please Login or Register  to view this content.

@Bo_ry: Mod() return wrong value if L2=350.1

[Bo_Ry]

change in red
=INDEX(B:B,MOD(MIN(INDEX(ABS(INDEX(C3:I19,,MATCH(K2,C2:I2,))-L2)*10^6+ROW(B3:B19),)),10^5))

[congnt92]

Hi Bo_ry
If so, with L2=350.11 then you must change the red number to 4 (10^4).

[excel_excel_excel]

Hi again guys, thank you so much for helping me. This is clearly a bit difficult for me. I didn`t excpect it to be so difficult. I try to understand what your`re posting here but I`m struggeling to understand it.
You have no idea how glad I would be if you could download the file attatched, edit the file and then upload it again.
I know it`s a lot to ask but I`ll try.
Again thank you very much, have a great day.

Almost there....xlsx

[Bo_Ry]

Copy and paste formula into K4

=INDEX(B:B,MOD(MIN(INDEX(ABS(INDEX(C3:I19,,MATCH(K2,C2:I2,))-L2)*10^9+ROW(B3:B19),)),10^3))

[kvsrinivasamurthy]

In L4

Please Login or Register  to view this content.