Vlookup not working for one item

[gjjh25]

Can anyone help with the enclosed please.

All the vlookup formulas work the 600, 675 etc, but when I select the 1200 it just returns N/A can anyone help please?

[JohnTopley]

Try this

=INDEX('Manhole Rings'!F4:J13,MATCH(F$6,'Manhole Rings'!$D$4:$D$13,0),MATCH(F$7,'Manhole Rings'$F$2:$J$2,0))

AND the heading in "Manhole Rings" is "1220 x 675 opening" not "1200 x 675 opening"

[Special-K]

The description on the Manhole Rings sheet says "1220" not "1200"

Cant you just use this?
INDEX(...MATCH(),MATCH())

=INDEX('Manhole Rings'!F$4:J$13,MATCH(F$6,'Manhole Rings'!$D$4:$D$13,0),MATCH(F$7,'Manhole Rings'!$F$2:$J$2,0))

[gjjh25]

Thank you for your help

[gjjh25]

Sorry I thought this was solved, but no matter what i try i still cant get the formula in the yellow boxes to work.

I have uploaded a new sample.

Thank you

[Special-K]

You still have LOOKUP( INDEX...

Why arent you using the INDEX (MATCH (... MATCH( ... solution that John and myself suggested?

[gjjh25]

I am using that for the problem i was having before and that is solved.

This is for a different cell, i have tried that but cant seem to get it to work

[JohnTopley]

This works

=INDEX('Manhole Rings'!F109:J117,MATCH(G$6,'Manhole Rings'!$D$109:$D$118,0),MATCH(G46,'Manhole Rings'!F107:J107,0))

put data in 900/cover (and you had not changed heading as per previous posts....The description on the Manhole Rings sheet says "1220" not "1200")


BUT you did not tell us which cell has the problem.

[gjjh25]

Hi John,

Is this for the new uploaded sample.xls? I dont have any 1220 on the manhole sheet now?

[JohnTopley]

Sorry .. my error!

My last post was formula in G98

[Jonmo1]

The data you had as headers in the book from your other thread was Numeric and sorted ascending.
In this book, the headers are TEXT, and NOT sorted in ascending order.
The lookup function depends on the data being sorted Ascending.

The Index(Match(),Match()) is probably a better solution.

And as Special-K pointed out, The manhole ring descriptions don't match between G46 and F107:J107
G46 = "1200 x 675 opening"
J107 on Manhole Rings tab = "1220 x 675 opening"

Change one of those 2 cells so they are both the same, and use
=INDEX('Manhole Rings'!$F$109:$J$118,MATCH(G$6,'Manhole Rings'!$D$109:$D$118,0),MATCH($G$46,'Manhole Rings'!$F$107:$J$107,0))

[gjjh25]

Sorry, I dont understand, is this on the latest sample I uploaded, as the cells i am having problems with are G98 G99 G100. G98 works ok now as per your suggestions

[JohnTopley]

G99 and G100 work.

in G102

=INDEX('Manhole Rings'!$F$123:$J$124,MATCH(G$50,'Manhole Rings'!$E$123:$E$124,0),MATCH(G$46,'Manhole Rings'!$F$121:$J$121,0))

[gjjh25]

thank you for the help