vlookup dynamic range

[a.hudrea]

Hey guys I need some help in finding the right formula for my situation.
I have 3 separate sheets: A, B, C. On each sheet i have two columns one with name one with price.
I want to create another sheet in which i can have one cell with name and to the right a cell with a formula that will retrieve the price
test.xlsx

[José Augusto]

Hi
use this formula
=IFERROR(VLOOKUP(C4,A!C3:D6,2,FALSE),IFERROR(VLOOKUP(C4,B!C3:D6,2,FALSE),IFERROR(VLOOKUP(C4,'C'!C3:D6,2,FALSE),"")))

Please Login or Register  to view this content.

Regards

[Debraj Roy]

Hi !

Try something like this..

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

test.xlsx

[FDibbins]

Try this...
=VLOOKUP(C4,IF(ISERROR(MATCH(C4,A!$C$3:$C$6,0)),IF(ISERROR(MATCH(C4,B!$C$3:$C$6,0)),'C'!$C$3:$D$6,B!$C$3:$D$6),A!$C$3:$D$6),2,0)

[a.hudrea]

thanks for the help guys ... awesome as always.

[FDibbins]

Happy to help and thanks for the feedback