Index, Match forumlar not working

[TheHobbit81]

Hi, (my first post)

I am currently trying to use an INDEX, MATCH formular to look at two cells and give the intersection based on the Index of another sheet.

I look at my formula (a few hours of staring at it) and it seems to be correct, but as you can from the attached example, it does not work.

Any help would be great.

The Hobbit 81

[rylo]

Hi

E12: =INDEX(Sheet2!$I$1:$L$4,MATCH(D12,Sheet2!$J$4:L$4,),MATCH(C12,Sheet2!$I$1:$I$3))


Copy down as required.

You may want to consider using an if statement to check for data if you are going to prefill the formula and data cells may not be completed if you want to avoid the #N/A error.


rylo

[TheHobbit81]

Hi,

I tried your formula, but it look Identical to the existing one.

When I did it though it did get rid of one of the "#N/A" errors.

Please see updated attached to see what I mean re: E12:F12

Hope to get this working, appreciate the help. :D

Hobbit

[corinereyes]

Hi,

Do you really need an Index, Match formula? Try the attached sheet.

[TheHobbit81]

Unfortunately, yes.

Please have a look at 'Sheet2' I have removed the test data and put in the actual information that I want it do 'draw' from.

Sorry.

[corinereyes]

Hi,

Try the attached sheet.

[TheHobbit81]

Thats it!!!

Perfect. Thanks heaps, looks like I was missing the 'match type.'

Thanks again

The Hobbit 81
~©¿©~
U

[corinereyes]

Your welcome, glad it helped.

[TheHobbit81]

Arrrgh,

Worked fine until I tried to transpose the formula into my main Worksheet. I have attached a stripped down version of the actual worksheet I am trying to get working.

What am I doing wrong?

[corinereyes]

Hi,

From the first example i posted, the formula for returning the column no. is in row 1, and now i've used row 4, cells J4:L4 sheet LookupTables.

Try this formula:

Please Login or Register  to view this content.

or try the attached sheet.

[TheHobbit81]

Thanks corinereyes

That has done the trick. You rock!!!

The Hobbit 81