Match Values In Two Sheets And Return Value At The Top Of The Column It Was Matched In

Hello All,

I'm attempting to figure out some sort of formula or function that will allow me to match a specific value in one sheet with a value in another sheet and then return the value that is in the top of the column it is in. I thought that maybe I would need to do some sort of VLOOKUP function but that won't work.. I looked into INDEX and MATCH but I'm completely clueless as to how to put it together. I'll attach the file that I'm using.. I want to be able to match the values in sheet A with values in sheet Master File. If the value occurs more than once in the Master File sheet then I would like for it to return the top value of that column as well.. and put it into the "Location" column. So If A13/4L150 is in the master file sheet then return the value of 4.17.3, 4.14.3, 4.13.2 etc etc... could this be done? Stocking.xlsx

Thanks in advance!

E2=IFERROR(INDEX('Master File'!$1:$1,MIN(INDEX(('Master File'!$A$2:$TG$576<>$A$1&$A2)*10^10+COLUMN('Master File'!$A$2:$TG$576),0))),"")

Please Login or Register  to view this content.

Try this and copy towards down in all sheets

This seems to work for me.

=IF(LARGE(INDEX(('Master File'!$B$2:$SV$50=A2)*(COLUMN($B$2:$SV$50)),),1)=0,"",INDEX('Master File'!$A$1:$SV$1,LARGE(INDEX(('Master File'!$B$2:$SV$50=A2)*(COLUMN($B$2:$SV$50)),),1)))

It only pulls the one column. If you expect results from multiple columns, maybe VBA is the was to go.

This formula will work on any sheet where you are searching MasterFile for matches. If you want to search a different sheet, just change whereever it says Master File and adjust the data range and headers range.

Unfortunately, there are no formulas that pull the formatting along with the cell. You would require a VBA solution to do something like that or use conditional formatting if the formatting follows some sort of rules.

I didn't download your file.

See if this helps...

Return ALL of the column headers if a column contains the number 1.

Data Range

	A	B	C	D	E
1	Header1	Header2	Header3		All Headers
2	11	25	72		Header1
3	1	34	63		Header3
4	99	29	1
5	90	40	37
6

This array formula** entered in E2:

=IFERROR(INDEX(A$1:C$1,SMALL(IF(A$2:C$5=1,COLUMN(A2:C5)-COLUMN(A2)+1),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

The issue is that he has multiple (like 10 sometimes) duplicates under the same header so the formula would have to remove duplicate headers at the same time.

Content deleted.

I guess I don't understand, then.

If I put a 1 in column B, it's only bringing back Header1 and header2 for some reason. Hmmmm

Originally Posted by ChemistB

If I put a 1 in column B, it's only bringing back Header1 and header2 for some reason. Hmmmm

Yeah, disregard that post. Bugs!

Ok, I think I have it working now.

Data Range

	A	B	C	D	E
5	Header1	Header2	Header3	------	Header1
6	59	47	1		Header3
7	1	91	38
8	1	35	1

This array formula** entered in E5:

=INDEX(A$5:C$5,SMALL(IF(COUNTIF(OFFSET(A$6,,COLUMN(A6:C8)-COLUMN(A6),ROWS(A$6:C$8),1),1)>0,COLUMN(A6:C8)-COLUMN(A6)+1),ROWS(E$5:E5)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Genius! Nice Job, Tony.

Thanks!

Dylan, if you're still out there, now that Tony has done all the hard work, I've modified his formula to work on the uploaded spreadsheet. In A!E2 copied down and across 3 columns (more if you think you'll need it), this Arrayed formula

=IFERROR(INDEX('Master File'!$B$1:$SV$1,SMALL(IF(COUNTIF(OFFSET('Master File'!$B$2,,COLUMN($B$2:$SU$50)-COLUMN($B$2),ROWS($B$2:$SU$50),1),$A2)>0,COLUMN($B$2:$SU$50)-COLUMN($B$2)+1),COLUMNS($A$1:A$1))),"")

See my attachment (I filtered SheetA column E to not show blanks.