Share this content

Excel help please!

Identifying a code number & returning data in a separate tab, without using Pivot Tables?

Didn't find your answer?

Hi Excel Gurus!
I have, let's say, two tabs in one Excel file.
In Tab One is a list of data in chronological order (column A: date; column B: info; column C: info; column D: £amount; column E: code number).
In Tab Two, I would like to achieve the following which I'm verbalising as: 'Look at Tab One & if Excel sees the code number in column E matching the code number associated with Tab Two, then 'return' i.e. place in columns A/B/C/D of Tab Two a copy of the data that is shown in the corresponding columns of Tab One'.
Pivot tables are a 'no' because the data may need to be amended later ~ this query just relates to an initial looking down a list & placing data matching a code number.
I know I could manually enter a relative cell reference in Tab Two (='Tab One!A1' etc) but I'm looking for something a bit more semi-automatic without being too rigid & over-engineered.
I've researched Excel online & feel like the solution is out there somewhere, but I'm stumped!
Any ideas, please?
Many thanks in advance...

Replies (11)

Please login or register to join the discussion.

avatar
By accountaholic
13th May 2021 13:33

Where is the "code number associated with tab two" found?

Could you use VLOOKUP - I'm not sure this works if the code in tab one is to the right of the data.

Possibly INDEX MATCH, very useful but can be difficult to figure out.

XLOOKUP might work
=XLOOKUP(Tab Two code number, range of code numbers in Tab One, Range of data in Tab One,0)

(The 0 at the end means exact match.)
(The third bit - range of data - would be something like Tab One!A1:A15) then alter for column B and so on.

Thanks (1)
Replying to accountaholic:
avatar
By paul.benny
13th May 2021 13:49

accountaholic wrote:
Could you use VLOOKUP - I'm not sure this works if the code in tab one is to the right of the data.

It doesn't. VLOOKUP requires the value to be in the leftmost column of the search range. Possibly the OP could reorder the data on sheet1 so that the code is in the first column?

Thanks (1)
By Duggimon
13th May 2021 13:48

I think it's VLOOKUP you want to use which I think, as accountaholic says, needs the data to have the column being referenced as the leftmost column as it works with an offset.

If you need the columns in the order they are then I'd suggest just making column AA - column E, then AB = A, AC = B and so on, then use columns AA - AE in the VLOOKUP function on the second tab.

It's still automatic, will update dynamically and isn't hard to write a function to copy to all cells, I think in tab 2, using my copying method, it would look like =VLOOKUP(A1, 'Tab One!$AA$1':'Tab One!$AE$1048576', [numbers 2 - 5 depending what data you want in the column], TRUE)

Thanks (1)
Replying to Duggimon:
By Duggimon
13th May 2021 15:02

Not TRUE, FALSE

I always mix that up.

Thanks (1)
avatar
By CMP Accountant
13th May 2021 16:01

Assuming the 'code number' is always unique on 'Tab One', then I would tackle this with an INDEX MATCH. If there can be multiple entries with the same 'code number' then you'd have to use some kind of array.

On 'Tab Two' I would suggest using cell A1 as your input (type the code number you want to search for), then in cell A4 use the formula as follows;

=INDEX('Tab One'!A2:E5,MATCH('Tab Two'!A1,'Tab One'!E2:E5,0),1)

The above will search for the 'code number' you type in cell A1, and return the data from the first column in the table (date).

In cell B4 you can past the above formula again, but at the end of the formula replace the 1 with a 2. That will now return the data in the second column of the table.

Paste the formula in to cells C4, and D4 - but replace the final number with a 3, & a 4. That will then return the data from columns 3 & 4 of the table.

I hope this helps.

Thanks (2)
Replying to CMP Accountant:
avatar
By Burlington Bertie
14th May 2021 14:32

CMP Accountant - yes, that does work for a single code in Tab One, many thanks indeed! But, as you suspected, in my case there would be many instances of the same code appearing in a chronologically-ordered list of multiple codes in Tab One. So, I will have to investigate how an array could help further...

Thanks (0)
Replying to Burlington Bertie:
avatar
By CMP Accountant
14th May 2021 15:58

I've just found a formula in Excel that looks like it is a very simple solution for your case, but it would appear it only works in the latest version - I seem to be a couple of version behind so can't test it myself.

https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb...

Thanks (2)
Replying to CMP Accountant:
avatar
By Burlington Bertie
18th May 2021 11:48

Hi CMP Accountant ~ many thanks for this. Yes, it does look like it does the job, but only in Office 365, like you say. Ho, hum...thanks anyway!

Thanks (0)
avatar
By Burlington Bertie
13th May 2021 18:53

Many thanks for all contributions ~ I'm very grateful for your input!
The code number on Tab Two could be placed anywhere that suits.
I take on board the point that the column being referenced needs to be on the left-hand-side ~ that can be easily remedied.
When I was surfing, I could see that some sort of VLOOKUP/INDEX/MATCH solution might be the way forward, but didn't have enough experience to fathom out the correct logical sequence.
So, many thanks again for all contributions & especially to CMP Accountant for the formula ~ I'll certainly give it a go!

Thanks (2)
Replying to Burlington Bertie:
avatar
By paul.benny
13th May 2021 19:42

Nice to see someone who acknowledges the help given. Too many dont. So thank you (That's enough mutal gratitude)

Thanks (1)
Replying to paul.benny:
avatar
By Burlington Bertie
14th May 2021 10:59

paul.benny thank you for thanking my thanks. Oh dear, where's this going?

Thanks (1)
Share this content