vlookup failing

[merlin777]

I have a table on one sheet which looks up values in a table on another sheet based on a string in column A (e.g. 'xps.l') and populates other columns in that row.

Until today it was working fine but I added another row to the first sheet with a new string and now it's returning #/NA for the lookup values for another one of the strings. The new one is fine.

The little ! offers up the error is 'A value is not available to the formula or function'.

My lookup table is sorted alphabetically, although my lookup function is '=VLOOKUP(A56,returneddata,4,FALSE)' so I'm not sure that's necessary.

Interestingly, the lookup string which has failed isn't the new one, its the one (xps.l) which is last in the lookup table.

I've checked spelling and punctuation etc and they are the same. The lookup table is only about 45 rows.

I'm mystified. Any ideas?

I should add that in spite of the error explanation, all the columns in the lookup table have data in them.

 

[Aladin Akyurek]

Does returneddata, which is apparently a named range, include the new row?

 

[merlin777]

you genius... thanks!

I thought I was so clever using named ranges, but when the cell ranges aren't there in front of you it's easy to forget them...

 

[Aladin Akyurek]

you genius... thanks!

You are welcome.

I thought I was so clever using named ranges, but when the cell ranges aren't there in front of you it's easy to forget them...

Quite so. Try to switch to dynamic named ranges if appropriate.