98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Data between Excel Tabs - IF Function
Data between Excel Tabs - IF Function
Resolved · 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
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...
Thu 28 Aug 2014: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Enter formulae into multiple cellsIf 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. |