data between excel tabs

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data between Excel Tabs - IF Function

Data between Excel Tabs - IF Function

resolvedResolved · Medium Priority · Version 2013

Jack has attended:
Project Intermediate course

Data between Excel Tabs - IF Function

Hello,

I was wondering if you might be able to help me with a function I want to build into a document I am putting together.

What I am trying to achieve is to, by entering a value in a tab that matches with another, generate information in a series of cells that matches with cells linked to the original.

For example, in tab X in column A there would be numbers 1, 2, 3 etc descending down rows 1, 2, 3. In the columns to the right there are various different bits of information.

Is there a formula that would mean that, if number 1, 2, 3 were entered anywhere in column A in tab Y, that it would then generate some/all of the information in the cells linked to that number (1,2,3) in tab X in a likewise fashion in tab Y?

I have considered whether an IF formula might work, but if I remember correctly this is limited to 7no. possibilities. For this document the possibilities may well eventually go beyond 100no. Is there an alternative way that would enable me to do this?

Thanks

Jack

Edited on Thu 21 Aug 2014, 09:28

RE: Data between Excel Tabs - IF Function

Hi Jack

Thanks for getting in touch. You're right that an IF function sounds like it would work, but the nesting limit of 7 is impractical for you.

A good alternative is to use a VLOOKUP. This function is generally for matching values across different lists to retrieve related information. Here's how it looks:

=VLOOKUP(lookup_value, table_array, col_index_num, lookup type)

or

=VLOOKUP(what you're searching on, where the data is, which column of info to get, exact match or not)

It's easier to explain with an example, so I've attached one for you. Let me know how you get on.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

VLOOKUP example.xlsx

RE: Data between Excel Tabs - IF Function

Hi Gary,

Thanks very much, that was exactly what I needed and worked perfectly.

Thanks very much for your help!

Jack

Thu 28 Aug 2014: Automatically marked as resolved.


 

Excel tip:

Enter formulae into multiple cells

If a formula is to be used in a series of cells, select the cells first. Now type in your formula and hold the Ctrl key while you press Enter. This enters the formula in each selected cell.

Just be careful with your absolute and relative cell references.

View all Excel hints and tips


Server loaded in 0.07 secs.