VLookup twice in same formula
In a spreadsheet I am doing a vlookup to see if a field in another sheet is a particular value. If it is not I use the value of the vlookup which I have to enter again. So, the formula looks like this:
=IF(VLOOKUP(TEXT($C2,"#"),'ESR report'!$D:$I,4,FALSE)="NULL",1,VLOOKUP(TEXT($C2,"#"),'ESR report'!$D:$I,4,FALSE))
Does anyone know of a neater way of doing this?
RE: VLookup twice in same formula
Hi Richard
Thanks for your question. Just checking, does your formula work?
I've tried something similar without the Text function part and it returns the value looked up or a 1 if the value is "null".
It seems fine and I can't see an easier way. Perhaps say a bit more about what's behind your query.
Regards
Doug
STL
RE: VLookup twice in same formula
Hi Doug
Thanks for the reply.
Yes, it works.
The Text function is used as I am looking up a number in a text field. If I don't change my number to text, then the formula fails.
The function extracting a date. In the lookup sheet this is either a date or the word "NULL" (not an empty field). So if it is Null, I set the returned value to 1 (1/1/1900), if it's not null then I use the value that vlookup finds.
So, I look up the date, set it as 1 if the date is "NULL", if not then I lookup the date again to find the value.
I guess I could add a macro to delete all "NULL" fields - or even change them to 1, but I don't want any manual steps, just add the data and get the results.
I can't see an easier way to do it (except with VBA), but wondered if any of you guys knew of some trick to make the repeated vlookup not necessary.
It's no big deal as it works anyway, I was just wondered if there was a more efficient way to do it.
Regards
Richard
