Horizontal Lookup Name Search

[nutrastat]

Hi,

I do hope somebody can help me. I have 2 sheets...

First Sheet

Section 1 Fred Bloggs 1001 1003 1005 1007 1009
Section 2 Amy Trams 1000 1002 1004 1006 1008

Second Sheet

1002 9.2 0.00% 1 <section name="" here="">1005 9.7 80.00% 2 <section name="" here="">1009 8.4 100.00% 1 <section name="" here="">

On the first sheet is the 'Section Sheet' with the Room numbers, and, on the second sheet, created by our internal systems, which as exported by CSV, which I copy into second sheet, there is the room number, with the statistics. I cannot work out how to put the <section name="" here=""> without using VBA; which we cannot use as all macro as disabled on our systems.

Can anybody help me with the code to replace with the appropriate Section Name. Obviously this is NOT on the sheet, it is just for this example.
<section name="" here=""><section name="" here="">
I look forward to seeing what help somebody can give me, as I normally using Index/Match but cannot fathom out how to do this on this occasion.</section></section></section></section></section></section>

 

[sandy666]

what is your excel version and show expected result from your example

 

[Yongle]

How about changing the layout of your sheet to make formula very easy

Create 2 tables in First Sheet
- using a structured table makes the ranges dynamic
- I renamed the tables Table_A and Table_B


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]Section[/td][td=bgcolor:#4472C4]Name[/td][td][/td][td]Room[/td][td]Section[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]Section 1[/td][td=bgcolor:#D9E1F2]Fred Bloggs[/td][td][/td][td=bgcolor:#E2EFDA]

1000​

[/td][td=bgcolor:#E2EFDA]Section 2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Section 2[/td][td]Amy Trams[/td][td][/td][td]

1001​

[/td][td]Section 1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

1002​

[/td][td=bgcolor:#E2EFDA]Section 2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

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

1003​

[/td][td]Section 1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

1004​

[/td][td=bgcolor:#E2EFDA]Section 2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

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

1005​

[/td][td]Section 1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

1006​

[/td][td=bgcolor:#E2EFDA]Section 2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

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

1007​

[/td][td]Section 1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]

1008​

[/td][td=bgcolor:#E2EFDA]Section 2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

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

1009​

[/td][td]Section 1[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: First Sheet[/td][/tr][/table]

and now the lookup formula is straightforward

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#E2EFDA]Room[/td][td]Score[/td][td]percent[/td][td]Your no ?[/td][td][/td][td=bgcolor:#E2EFDA]SECTION[/td][td] Formula copied down[/td][td=bgcolor:#E2EFDA]Name[/td][td] Formula copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#E2EFDA]

1002​

[/td][td]

9.2​

[/td][td]

0.00%​

[/td][td]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]Section 2[/td][td=bgcolor:#F8CBAD] =VLOOKUP(A2,Table_B,2,0)[/td][td=bgcolor:#E2EFDA]Amy Trams[/td][td=bgcolor:#F8CBAD] =VLOOKUP(F2,Table_A,2,0)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#E2EFDA]

1005​

[/td][td]

9.7​

[/td][td]

80.00%​

[/td][td]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]Section 1[/td][td][/td][td=bgcolor:#E2EFDA]Fred Bloggs[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#E2EFDA]

1009​

[/td][td]

8.4​

[/td][td]

100.00%​

[/td][td]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]Section 1[/td][td][/td][td=bgcolor:#E2EFDA]Fred Bloggs[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Second Sheet[/td][/tr][/table]

 

[nutrastat]

what is your excel version and show expected result from your example

My Excel Version is the latest 2018.

Sorry, I can now see it has taken out my writing in angled brackets.

My expect result would be:

1002 9.2 0.00% 1 Section 2
1005 9.7 80.00% 2 Section 1
1009 8.4 100.00% 1 Section 1


I am trying to put the SECTION NUMBERS at the end of the Stats Page, so that I can do a further lookup/index match

 

[nutrastat]

Hi Yongle,

Thanks for your prompt response.

Yes, this is what they had before, BUT, I am trying to work this for 16 separate locations with variable Section numbers & rooms.

My thinking, as I have more to do, is that I can make it fully automatic from them just completing the Setup sheet with all Sections, Names, and Room numbers, and then on the second sheet they just copy in the CSV file. Then, with all the other statistics that need to be done, I hope to be able to work it from this completed sheet. I just need the Section Number at the end and cannot work out how to do this. Ormally, I have no problem with lookups, but this one is not coming for me.

 

[sandy666]

My Excel Version is the latest 2018.

It's Mac so I give up

 

[Yongle]

(tested on Windows Excel 365)

Here is an example that does EXACTLY what you want - read the link first
https://exceljet.net/formula/index-and-match-on-multiple-columns

That example was easily be applied to your example data
- with assumption that data in First Sheet is in range A2:CZ20
- I suggest avoiding using complete columns in lookup ranges in case Excel runs out of resources
- simply ensure that ranges are more than big enough in both directions
- named range Sections is equiv of Groups in example
- named range Rooms is equiv of Names in example
- named range Manager is used in the lookup in column H

An array formula must be confirmed using {CTRL}{SHIFT}{ENTER} and then the formula is enclosed in { } (cannot type those in!!)

RefersTo formula for named ranges used

Sections
='First Sheet'!$A$2:$A$20

Rooms
='First Sheet'!$C$2:$CZ$20

Manager (this is used in index and match formula in H2)
='First Sheet'!$B$2:$B$20


Formulas in cells

Array formula in F2 copied down
=INDEX(Sections,MATCH(1,MMULT(--(Rooms=A2),TRANSPOSE(COLUMN(Rooms)^0)),0))

will look like this if entered correctly with {CTRL}{SHIFT}{ENTER}
{=INDEX(Sections,MATCH(1,MMULT(--(Rooms=A2),TRANSPOSE(COLUMN(Rooms)^0)),0))}

Normal formula in H2 copied down
=INDEX(Manager,MATCH(F2,Sections,0))


Result in Second Sheet

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#E2EFDA]Room[/td][td]Score[/td][td]percent[/td][td]Your no ?[/td][td][/td][td]SECTION[/td][td] Formula copied down[/td][td]Name[/td][td] Formula copied down[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#E2EFDA]

1002​

[/td][td]

9.2​

[/td][td]

0.00%​

[/td][td]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]Section 2[/td][td=bgcolor:#F8CBAD] {=INDEX(Sections,MATCH(1,MMULT(--(Rooms=A2),TRANSPOSE(COLUMN(Rooms)^0)),0))}[/td][td=bgcolor:#E2EFDA]Amy Trams[/td][td=bgcolor:#F8CBAD] =INDEX(Manager,MATCH(F2,Sections,0))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#E2EFDA]

1005​

[/td][td]

9.7​

[/td][td]

80.00%​

[/td][td]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]Section 1[/td][td][/td][td=bgcolor:#E2EFDA]Fred Bloggs[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#E2EFDA]

1009​

[/td][td]

8.4​

[/td][td]

100.00%​

[/td][td]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]Section 1[/td][td][/td][td=bgcolor:#E2EFDA]Fred Bloggs[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Second Sheet[/td][/tr][/table]


First Sheet - layout in my example

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Section 1[/td][td]Fred Bloggs[/td][td]

1001​

[/td][td]

1003​

[/td][td]

1005​

[/td][td]

1007​

[/td][td]

1009​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Section 2[/td][td]Amy Trams[/td][td]

1000​

[/td][td]

1002​

[/td][td]

1004​

[/td][td]

1006​

[/td][td]

1008​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: First Sheet[/td][/tr][/table]

 

[nutrastat]

Sorry, I should have said Office 365, latest version.

 

[nutrastat]

Thank you, I am currently training and people are just about to arrive. GIve me a few hours and I will revert when I have had time to work on this. FYI: No wonder I could not work it out!!