1 match in multiple columns

[orion23]

Good morning All,

I need help with a formula to match a unique value.

Excel book has 2 sheets
Sheet1 and Sheet 2
Sheet1 has the unique value I need to match
Sheet 2 has 3 columns with possible matches (D,E,F)
The resulting value should be a static column in sheet2, let's say column "H"

How can this be done?

Thank you in advance for your support

 

[etaf]

will sheet 2 have the same value multiple times

say sheet 1 has name fred

will fred appear once in columns D,E,F - or many times
if many times - what maybe in column H - all the same or different results

this was the sort of thing i was thinking about

Index and match on multiple columns

To lookup a value by matching across multiple columns, you can use an array formula based on several functions, including MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0))} where "names" is...

exceljet.net

on the same sheet not different sheets - but same sort of idea

 

[orion23]

Thank you for taking the time to reply

The value should only appear once in the multiple columns

The result is also unique in column H

Search for A2 (sheet1)
in sheet2, columns D, E, and F
result should be from sheet2, column H (static)

 

[etaf]

so that link should do it for you

but what version of Excel do you have - may need a later version, I'm using 365 version

I just need to check what versions those functions came in
mmult is quite old MMULT function - Microsoft Support
transpose is also old TRANSPOSE function - Microsoft Support

it is an array so depending on version you may need control+shift+enter and get {} around formula

here is a mockup

lookup grid -ETAF.xlsx
	A	B
1
2	fred	sheet2-h5
3	john	sheet2-h7
4	1	sheet2-h4
5	2	sheet2-h3
6	3	sheet2-h9
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=INDEX(Sheet2!$H$2:$H$10,MATCH(1,MMULT(--(Sheet2!$D$2:$F$10=Sheet1!A2),TRANSPOSE(COLUMN(Sheet2!$D$2:$F$10)^0)),0))

lookup grid -ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1											check
2								sheet2-h2			fred	sheet2-h5
3						2		sheet2-h3			john	sheet2-h7
4				1				sheet2-h4			1	sheet2-h4
5				fred				sheet2-h5			2	sheet2-h3
6								sheet2-h6			3	sheet2-h9
7						john		sheet2-h7
8								sheet2-h8
9						3		sheet2-h9
10					harry			sheet2-h10
11
Sheet2

I have but on dropbox, BUT will only be on for a few days - then I delete

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[orion23]

Thank you again..
Great, your formula works!!!!!!!!!!! Thank you
Unfortunately I thought that the value in D, E, F wouldn't repeat, but it does repeat at times
Changing the "Match(1" to Match (2 and to Match(3 helps to match the results by running the formula multiple times.

Is there a workaround?

 

[etaf]

whats in column H - are they different ?
how will excel know which ones to return
otherwise - if H is the same for all the entries , then it doesnt matter

what version of excel ?

 

[orion23]

Microsoft® Excel® for Microsoft 365 MSO (Version 2301 Build 16.0.16026.20002) 64-bit

Column H has unique values that don't repeat

Values in D, E & F will only repeat within the same row

and my apologies for providing incomplete information

 

[etaf]

IGNORE ---- I see what you mean now

so it does not matter if they repeat in the same row - as it will bring back H for that row anyway

I may be missing something, otherwise you would not have come back , but not sure what.....

so may need a small sample with expected results

also it would be worth updating your profile , to show the excel version - will help a lot for any future questions knowing the version as loads of new functions in 365

 

[etaf]

really messy - sure there is a way , will need to look into further
BUt off the top of my head

BUT
=IFERROR(INDEX(Sheet2!$H$2:$H$10,MATCH(1,MMULT(--(Sheet2!$D$2:$F$10=Sheet1!A2),TRANSPOSE(COLUMN(Sheet2!$D$2:$F$10)^0)),0)),IFERROR(INDEX(Sheet2!$H$2:$H$10,MATCH(2,MMULT(--(Sheet2!$D$2:$F$10=Sheet1!A2),TRANSPOSE(COLUMN(Sheet2!$D$2:$F$10)^0)),0)),INDEX(Sheet2!$H$2:$H$10,MATCH(3,MMULT(--(Sheet2!$D$2:$F$10=Sheet1!A2),TRANSPOSE(COLUMN(Sheet2!$D$2:$F$10)^0)),0))))

lookup grid -ETAF-2.xlsx

Shared with Dropbox

www.dropbox.com

 

[orion23]

Perfect, no need to look further.
Your initial approach was great, but this is even better
Thank you for going out of your way to help
Have a great week ahead!