Nested IF and VLOOKUP formula?

Hi everyone,

I've been stumped on this for a while and keep coming back to it to try and figure something out but so far I've had no luck.

I'm trying to generate approximate delivery dates based on the sender and receiver locations.

So far I have managed to create a bit of a messy IF formula which seems to almost be working backwards.

I have tried to write a formula that says if column E equals "GTC" then look in the "TOGTC" table on Sheet3 to return the number next to the match from column D(Sheet1) and add the date that is in column F to it.

The next part of the formula is supposed to be another condition but I've onbviously done something wrong as it is taking this for the IF FALSE condition.

Basically what I'm trying to say is, is it possible to have multiple IF TRUE conditions that all require VLOOKUP formulas?


Thanks for your help,

Alex

Originally Posted by Ace_XL

Try

=IFERROR(VLOOKUP(D2,INDIRECT("to"&E2),2,FALSE)+F2,"")

Copy down

Thank you so much! this works perfectly...will it work for all of my 10 tables in Sheet3?

Also, could you please talk me through this formula if possible? For future reference.

Thanks again

Originally Posted by benishiryo

hi alexander. it will work for all your 10 tables since all of your named range are typed as TOLocation. what Ace_XL was to combine the word "to" to the E2 (where it was dispatched to) and put them in a formula called INDIRECT.
"to"&E2 gives you "toGTC". but you can't do a VLOOKUP inside this text and return a value. you need to let Excel know that "toGTC" is a Named Range. hence, INDIRECT was used in this instance. so it will work if Column E has your Dispatched destination and your Named Range is TOLocation.

IFERROR is then to ensure that if the VLOOKUP formula encounters an error (not found in this case), it returns a blanks

Thank you very much that makes sense to me now...I'm learning slowly but surely! Thanks again