If vlookup produces N/A results then takes value from another col.

Hello, Please could someone help with the following.

SEE BELOW FOR CLEARER EXPLANATION

Description doesnt make sense.

This

=VLOOKUP(A1,Sheet2!A1:B1,2,0)

is already taking the value from column B (of Sheet 2).

Please explain clearly.

Or do you mean column B of the same sheet that the formula is on? If so

=IFERROR(VLOOKUP(A1,Sheet2!A1:B1,2,0),B1)
Or something else?

??
With this:
=VLOOKUP(A1,Sheet2!A1:B1,2,0)
you want B1 value ANYWAY
So:
=Sheet2!B1
is enough.

Ah yes, I do apologise - I have tried attaching a file but I am unable too for some reason


So,

Col A1+2
Example 1
Example 2

Col B1+2
5
5


Col C1+2 (formula)

=VLOOKUP(A1,D$1:E$1,2,0) THIS PRODUCES 1
=VLOOKUP(A2,D$1:E$1,2,0) THIS PRODUCES #N/A



Col D

EXAMPLE 1

Col E

1

In Col C when the vlookup produces #N/A value I want it to then take the value from Col B so Col C looks like


1
5


My data is much much bigger - just using the above as an example

Originally Posted by bebo021999

??
With this:
=VLOOKUP(A1,Sheet2!A1:B1,2,0)
you want B1 value ANYWAY
So:
=Sheet2!B1
is enough.

But OP is saying that could return #NA which he doesnt want (he wants value "from col B" - but hasnt specified what sheet).

@batexcel: Attaching files

Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

You've got circular references in that file, formulas that refer to the cell they are in.

Like this?

in C2
=IFERROR(VLOOKUP(A2,D$1:E$1,2,0),B2)