Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Lookup Value Across Multiple Worksheets

1. I agreed to these rules
Join Date
24th April 2008
Posts
4

## Lookup Value Across Multiple Worksheets

Hi,

Would like to lookup values across multiple worksheets. I am able to do this for a single worksheet using OFFSET(Karthik!\$E\$1,MATCH(A2,Karthik!E2:E21,0),-3).My Work Sheet Names are Karthik & Ramesh. How can i reference these worksheets in the above formula?

Pls help.

Thanks

Socratophile

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Lookup Value In Multiple Worksheets

Sorry, I don't understand what you mean by looking up across multiple worksheets.

Do you want to look in one sheet and, if the lookup can't be found, look in the next worksheet?

Or do you want to lookup the same value in two sheets and combine the results somehow?

Vlookup Across Excel Worksheets
Last edited by Dave Hawley; April 24th, 2008 at 18:18.

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
24th April 2008
Posts
4

## Re: Lookup Value In Multiple Worksheets

The value that I am looking up appears only in one of the sheets. So if the lookup can't be found in one sheet, look in the next worksheet.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Lookup Value In Multiple Worksheets

Hi,

If the return value is numeric then try

=LOOKUP(9.99999E+307,CHOOSE({1,2,3},0,INDEX(Ramesh!B2:B21,MATCH(A2,Ramesh!E2:E21,0)),INDEX(Karthik!B2:B21,MATCH(A2,Karthik!E2:E21,0))))

if it's text then

HTH
Auto Merged Post Until 24 Hrs Passes;

If it's both then

in a spare cell (e.g C2),

And try

You could hide column C or change the font color of C2.
Last edited by Krishnakumar; April 24th, 2008 at 17:34. Reason: Auto Merged Doublepost

5. I agreed to these rules
Join Date
24th April 2008
Posts
4

## Re: Lookup Value In Multiple Worksheets

Thanks for the reply Kris. The formula did not work. Let me explain. I have a bunch of values listed in a worksheet under column A. These values are also present in either sheets "Karthik" or "Ramesh" (part of the same workbook) and not both.

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Lookup Value In Multiple Worksheets

Vlookup Across Excel Worksheets

Vlookup Across Excel Worksheets

7. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Lookup Value In Multiple Worksheets

Originally Posted by socratophile
Thanks for the reply Kris. The formula did not work. Let me explain. I have a bunch of values listed in a worksheet under column A. These values are also present in either sheets "Karthik" or "Ramesh" (part of the same workbook) and not both.
That's exactly what my formula does. If it doesn't work for you, attach the workbook here.

8. I agreed to these rules
Join Date
24th April 2008
Posts
4

## Re: Lookup Value Across Multiple Worksheets

Hi Kris,

Your formula worked! Guess i just had to understand the logic and map the correct values. Thank you so much for this.

Have a nice day!

Regards,

Soc
Auto Merged Post Until 24 Hrs Passes;

Kris,

When i use the formula for a numeric value I get a "0" for references where there is no value. Is there anyway i can get the formula to diaplay a blank in such casesand not a 0 ?

Regards,

Soc
Last edited by socratophile; April 29th, 2008 at 12:52. Reason: Auto Merged Doublepost

Excel Video Tutorials / Excel Dashboards Reports

9. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Lookup Value Across Multiple Worksheets

Hi,

Custom format the cell

[=0]"";General

HTH

10. I agreed to these rules
Join Date
1st October 2012
Posts
1

## Re: Lookup Value Across Multiple Worksheets

hi,

this surely works on the same workbook ,
how about doing the same across different workbooks ?

thankss

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno