Help with nested IF function

[Vibeke]

I have a function that works fine:
=IF(ISERROR(VLOOKUP(G3,'Sheet1'!$C$1:$D$30,2,0)),"",VLOOKUP(G3,'Sheet1'!$C$1:$D$30,2,0))

My difficulty is, I want to expand this formula to include another
IF(ISERROR(VLOOKUP) function, to look up G3 in Column E and if found, return
the value in Column F. I've fiddled around with OR, but am receiving a
#Value! Columns C and E are in ascending order and do not have duplicates.

Any help gratefully received.
Vibeke

 

[David Biddulph]

If G3 will occur only in C or E, but not both, or if you want to give
preference to C over E, then try
=IF(ISERROR(VLOOKUP(G3,'Sheet1'!$C$1:$D$30,2,0)),IF(ISERROR(VLOOKUP(G3,'Sheet1'!$E$1:$F$30,2,0)),"",VLOOKUP(G3,'Sheet1'!$E$1:$F$30,2,0)),VLOOKUP(G3,'Sheet1'!$C$1:$D$30,2,0))

If G3 might occur in both C and E, and you don't want to give preference to
one column and you want to combine them in some way, then you need to
specify what you want the response to be.

 

[Vibeke]

Hi David - Yes, G3 will only occur once in C or E. Thank you for your help,
makes perfect sense when I looked at it! As usual I over-complicated things.
Many thanks, kind regards, happy holidays, etc.

 

[T. Valko]

You might be able to use this:

=IF(G3="","",VLOOKUP(G3,IF(COUNTIF(Sheet1!C1:C30,G3),Sheet1!C130,Sheet1!E1:F30),2,0))

 

[Vibeke]

That works too, and is shorter - the only drawback being that if the value in
G3 isn't found in Column C or E, then it returns a #N/A! result...but I can
make that go away. Many thanks for your suggestion.