Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

vlookup twice same formula

Forum home » Delegate support and help forum » Microsoft Excel Training and help » VLookup twice in same formula

VLookup twice in same formula

ResolvedVersion 2010

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

Excel tip:

Return to the active cell after scrolling

When I scroll a long way down the screen from a selected cell, I can return to that cell with the Ctrl+Back Space shortcut. The active cell now appears in roughly the middle of the screen.

Shift+Back Space does something similar. Scroll down from the active cell and Shift+Back Space returns me to it and puts the active cell at the top of the screen; scroll up from the active cell and Shift+Back Space returns me to it and puts the active cell at the bottom of the screen.

Note also, that while Ctrl+Back Space will return me back to a selected range, Shift+Back Space only ever returns me to the active cell, which is normally at the top left-hand corner of any selected range.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.