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
VLOOKUP example.xlsx
