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:

[TABLE="width: 364"]
<colgroup><col span="4"></colgroup><tbody>[TR]
[TD][TABLE="width: 445"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD]SCANNERID_1[/TD]
[TD]SCANNERID_2[/TD]
[TD]SCANNERID_3[/TD]
[TD]SCANNERID_4[/TD]
[TD] CheckinID[/TD]
[/TR]
[TR]
[TD]ND200_IS[/TD]
[TD]NDSP2PS[/TD]
[TD]NHBG308[/TD]
[TD]ND2_2AS[/TD]
[TD]ND200_IS[/TD]
[/TR]
[TR]
[TD]NDNR[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NDSP1PS[/TD]
[TD]ND100_IS[/TD]
[TD]NHBG308[/TD]
[TD]ND2_2AS[/TD]
[TD]ND100_IS[/TD]
[/TR]
[TR]
[TD]NCI711IS[/TD]
[TD]NHBG308[/TD]
[TD]ND2_2AS[/TD]
[TD]ND200_IS[/TD]
[TD]ND200_IS[/TD]
[/TR]
[TR]
[TD]NHBG308[/TD]
[TD]NDSP1PS[/TD]
[TD]ND100_IS[/TD]
[TD]ND2_2AS[/TD]
[TD]ND100_IS[/TD]
[/TR]
[TR]
[TD]ND150_IS[/TD]
[TD]NDSP1PS[/TD]
[TD]NHBG308[/TD]
[TD]ND2_2AS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NCI711IS[/TD]
[TD]ND700_IS[/TD]
[TD]NDSP3PS[/TD]
[TD]NT4AS[/TD]
[TD]ND700_IS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

SCANNERID_1​

[/td][td]

SCANNERID_2​

[/td][td]

SCANNERID_3​

[/td][td]

SCANNERID_4​

[/td][td]

CheckinID​

[/td][td][/td][td]

List​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

ND200_IS​

[/td][td]

NDSP2PS​

[/td][td]

NHBG308​

[/td][td]

ND2_2AS​

[/td][td]

ND200_IS​

[/td][td][/td][td]

ND100_IS​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

NDNR​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

ND200_IS​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

NDSP1PS​

[/td][td]

ND100_IS​

[/td][td]

NHBG308​

[/td][td]

ND2_2AS​

[/td][td]

ND100_IS​

[/td][td][/td][td]

ND700_IS​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

NCI711IS​

[/td][td]

NHBG308​

[/td][td]

ND2_2AS​

[/td][td]

ND200_IS​

[/td][td]

ND200_IS​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

NHBG308​

[/td][td]

NDSP1PS​

[/td][td]

ND100_IS​

[/td][td]

ND2_2AS​

[/td][td]

ND100_IS​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

ND150_IS​

[/td][td]

NDSP1PS​

[/td][td]

NHBG308​

[/td][td]

ND2_2AS​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

NCI711IS​

[/td][td]

ND700_IS​

[/td][td]

NDSP3PS​

[/td][td]

NT4AS​

[/td][td]

ND700_IS​

[/td][td][/td][td][/td][/tr]
[/table]

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