vlookup from either of 2 worksheets

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
My INPUT data is in 2 worksheets. Sheet1 & Sheet2.
Output is Sheet3.
In Sheet3, I want to perform vlookup & get data of Sheet1 IF EXISTING else data of Sheet2.
Ex;
Sheet1: A1=#N/A
Sheet2: G1=500
Sheet3: B1=500

How to achieve?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
My INPUT data is in 2 worksheets. Sheet1 & Sheet2.
Output is Sheet3.
In Sheet3, I want to perform vlookup & get data of Sheet1 IF EXISTING else data of Sheet2.
Ex;
Sheet1: A1=#N/A
Sheet2: G1=500
Sheet3: B1=500

How to achieve?

Your specs are incomplete...

Sheet3, B1

=IFERROR(LOOKUP(9.99999999999999E+307,CHOOSE({1,2},VLOOKUP(A1,Sheet2!F:G,2,0),VLOOKUP(A1,Sheet1!F:G,2,0))),"")
 
Upvote 0
To complete,
Sheet1: A1 PPP & G1=#N/A
Sheet2: A1 PPP & G1=500
Sheet3: A1 PPP.
So in B1, 500 is answer.
 
Upvote 0
To complete,
Sheet1: A1 PPP & G1=#N/A
Sheet2: A1 PPP & G1=500
Sheet3: A1 PPP.
So in B1, 500 is answer.

I'm sure your specs are clear to you, but not me. The topic title mentions vlookup, which ordinarily presupposes a table, that is, a multicell range. I don't see that you mention above any such table...
 
Upvote 0
I am sorry. I'll try to make more clearer. Please bear with me.....
Sheet1: A1:A999 P1,P2,P3....P999
Sheet2: G1:G999 P1000, P1001, ....... P1999
So in Sheet3 in B1: for A1=P1 get X1 (from Sheet1) but if A1=P1000 get X1 (from Sheet2).

Ihope i have clarified.
 
Upvote 0
I am sorry. I'll try to make more clearer. Please bear with me.....
Sheet1: A1:A999 P1,P2,P3....P999
Sheet2: G1:G999 P1000, P1001, ....... P1999
So in Sheet3 in B1: for A1=P1 get X1 (from Sheet1) but if A1=P1000 get X1 (from Sheet2).

Ihope i have clarified.

What is X1?

Taking up your Sheet2 spec literally...
Rich (BB code):
=IFERROR(LOOKUP(9.99999999999999E+307,CHOOSE({1,2},
  INDEX(Sheet2!$P$1000:$P$1999,MATCH(A1,Sheet2!$A$1:$A$999,0)),
  INDEX(Sheet1!$P$1:$P$999,MATCH(A1,Sheet2!$G$1:$G$999,0)))),"")
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top