Look for a cell value then return row header

[RachelN76]

Hi All,

I have two separate workbooks, and I need to check all the values in WB1, Sheet1, Column C to see if they are anywhere in WB2.
I thought this might be a bit of a big ask, so I've created a helper sheet in WB2 that has columns A:L with column headers and the values I'm looking for in A2:L50

I then thought it would be a relatively simple INDEX MATCH type thing to look for the values in WB1 Col C, and then if it's found in column D, return D1. But I can't now think how to do it without some kind of searching macro (not ideal, have to get special permission from IT for this!), or using a lot of nested IFs (again not ideal because WB1 is already massive and I fear this might break it). I'm sure there should be a really simple solution, but I just can't find it today.

Can you help?

 

[Cubist]

Which version of Excel are you using?
Post a sample of your data and the expected output.

 

[RachelN76]

Sorry it's taken so long to get back - I'm using Excel 365.

I have a worksheet with lots of data in the form of the first table.
Then in a separate workbook I have a long list of numbers like column 'Screened' in the second table.
I want to look at each number in that column, compare it to the whole of the first table, and if there's a match, then return that row header.

I can find the matching numbers easily enough, but I don't know what I can do to then look upwards to find the row header?
I feel there should be a really simple way to do this, but I can't do it!

Trial1	Trial2	Trial3
123456	123789	159871
456789	321654	357462
789456	654987	685412

Screened	Matched
123456	Trial1
456789	Trial1
321654	Trial2
456123
741258
963258
159871	Trial 3

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Cubist]

Try:

Book2
	A	B	C	D	E	F
1	Trial1	Trial2	Trial3		Screened	Matched
2	123456	123789	159871		123456	Trial1
3	456789	321654	357462		456789	Trial1
4	789456	654987	685412		321654	Trial2
5					456123
6					741258
7					963258
8					159871	Trial3
Sheet2

Cell Formulas
Range	Formula
F2:F8	F2	=IFNA(INDEX(A1:C1,ROUNDUP(XMATCH(E2:E8,TOCOL(A2:C4,,1))/COLUMNS(A1:C1),0)),"")
Dynamic array formulas.

 

[Cubist]

Another option with XLOOKUP:

=XLOOKUP(E2:E8,TOCOL(A2:C4,,1),TOCOL(IF(SEQUENCE(COLUMNS(A1:C1))<=COLUMNS(A1:C1),A1:C1),,1),"")

 

[RachelN76]

Thank you so much. That's amazing.
I've amended the first one and got it working - but the results are offset by 1 column, and I just can't work out why. It was fine on my trial data, but when I put it into the big spreadsheets (and had to adjust things a bit more) that's where it's happened.
So now if a value actually appears in Trial1 column, it will return Trial2 - have you any idea how I've messed this up?

 

[Cubist]

Can you show how you applied your formula?

 

[RachelN76]

=IFNA(INDEX('[2. HAEM Tracker.xlsx]Helper'!A1:D1,ROUNDUP(XMATCH((TEXTAFTER(D2:D2000,"H")),TOCOL('[2. HAEM Tracker.xlsx]Helper'!A2:D300,,1))/COLUMNS('[2. HAEM Tracker.xlsx]Helper'!A1:D1),0)),"")

Had to add in the 'textafter' part because some of our systems add a H to identifying numbers, whilst others don't. Other than that, I just changed the ranges. But they are in two different workbooks.

 

[Cubist]

I don't see any reason why it cause an offset. As far as the TEXTAFTER concerned, it's hard to say without looking at your actual data. It's mostly likely because you have a mix of numeric and text data. Try this:

=IFNA(INDEX('[2. HAEM Tracker.xlsx]Helper'!A1:D1,ROUNDUP(XMATCH((--TEXTAFTER(D2:D2000,"H",,,,D2:D2000),--TOCOL('[2. HAEM Tracker.xlsx]Helper'!A2:D300,,1))/COLUMNS('[2. HAEM Tracker.xlsx]Helper'!A1:D1),0)),"")