Return multiple values in a row using Hlookup or Index(Match)

[AlejandroD]

Hi Everyone,
I am trying to figure out how to write an Hlookup or an Index(Match) formula that shows multiple results. I want to search multiple values horizontally instead of vertically. Please see below as an example:

Criteria:

SCANNERID_1 SCANNERID_2 SCANNERID_3 SCANNERID_4 CheckinID ND200_IS NDSP2PS NHBG308 ND2_2AS ND200_IS NDNR NDSP1PS ND100_IS NHBG308 ND2_2AS ND100_IS NCI711IS NHBG308 ND2_2AS ND200_IS ND200_IS NHBG308 NDSP1PS ND100_IS ND2_2AS ND100_IS ND150_IS NDSP1PS NHBG308 ND2_2AS NCI711IS ND700_IS NDSP3PS NT4AS ND700_IS <colgroup><col span="4"><col></colgroup><tbody> </tbody>

<colgroup><col span="4"></colgroup><tbody>
</tbody>

Desired result:
I would like to add a formula on column E (CheckinID) that finds the values ND100_IS, ND200_IS, ND700_IS on each row. If the value is not found then the cell should be blank.

Hope it makes sense, thanks in advanced for your help.

 

[Fluff]

Hi & welcome to MrExcel.
How about
=IFERROR(INDEX(A2:D2,MATCH("ND100_IS",A2:D2,0)),IFERROR(INDEX(A2:D2,MATCH("ND200_IS",A2:D2,0)),IFERROR(INDEX(A2:D2,MATCH("ND700_IS",A2:D2,0)),"")))

 

[PCL]

Try
E2=IF(ISERROR(MATCH("ND100_IS",A2:D2,0)),IF(ISERROR(MATCH("ND200_IS",A2:D2,0)),IF(ISERROR(MATCH("ND700_IS",A2:D2,0)),"","ND700_IS"),"ND200_IS"),"ND100_IS")

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​
1​	SCANNERID_1​	SCANNERID_2​	SCANNERID_3​	SCANNERID_4​	CheckinID​		List​
2​	ND200_IS​	NDSP2PS​	NHBG308​	ND2_2AS​	ND200_IS​		ND100_IS​
3​	NDNR​						ND200_IS​
4​	NDSP1PS​	ND100_IS​	NHBG308​	ND2_2AS​	ND100_IS​		ND700_IS​
5​	NCI711IS​	NHBG308​	ND2_2AS​	ND200_IS​	ND200_IS​
6​	NHBG308​	NDSP1PS​	ND100_IS​	ND2_2AS​	ND100_IS​
7​	ND150_IS​	NDSP1PS​	NHBG308​	ND2_2AS​
8​	NCI711IS​	ND700_IS​	NDSP3PS​	NT4AS​	ND700_IS​

List in G2:G4

Formula in E2 copied down
=IFERROR(LOOKUP(9.99E+307,MATCH(G$2:G$4,A2:E2,0),G$2:G$4),"")

M.

 

[AlejandroD]

Thank you guys for your help and quick response, much appreciated!

 

[Fluff]

Glad we could help & thanks for the feedback