Horizontal Lookup Name Search

nutrastat

Board Regular
Joined
Nov 1, 2008
Messages
55
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>
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
what is your excel version and show expected result from your example
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
A
B
C
D
E
F
1
SectionNameRoomSection
2
Section 1Fred Bloggs
1000
Section 2
3
Section 2Amy Trams
1001
Section 1
4
1002
Section 2
5
1003
Section 1
6
1004
Section 2
7
1005
Section 1
8
1006
Section 2
9
1007
Section 1
10
1008
Section 2
11
1009
Section 1
12
Sheet: First Sheet

and now the lookup formula is straightforward

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
1
RoomScorepercentYour no ?SECTION Formula copied downName Formula copied down
2
1002​
9.2​
0.00%​
1​
Section 2 =VLOOKUP(A2,Table_B,2,0)Amy Trams =VLOOKUP(F2,Table_A,2,0)
3
1005​
9.7​
80.00%​
2​
Section 1Fred Bloggs
4
1009​
8.4​
100.00%​
1​
Section 1Fred Bloggs
5
Sheet: Second Sheet
 
Last edited:

nutrastat

Board Regular
Joined
Nov 1, 2008
Messages
55
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

Board Regular
Joined
Nov 1, 2008
Messages
55

ADVERTISEMENT

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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

(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
A
B
C
D
E
F
G
H
I
1
RoomScorepercentYour no ?SECTION Formula copied downName Formula copied down
2
1002​
9.2​
0.00%​
1​
Section 2 {=INDEX(Sections,MATCH(1,MMULT(--(Rooms=A2),TRANSPOSE(COLUMN(Rooms)^0)),0))}Amy Trams =INDEX(Manager,MATCH(F2,Sections,0))
3
1005​
9.7​
80.00%​
2​
Section 1Fred Bloggs
4
1009​
8.4​
100.00%​
1​
Section 1Fred Bloggs
Sheet: Second Sheet


First Sheet - layout in my example

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
2
Section 1Fred Bloggs
1001​
1003​
1005​
1007​
1009​
3
Section 2Amy Trams
1000​
1002​
1004​
1006​
1008​
4
5
Sheet: First Sheet
 

nutrastat

Board Regular
Joined
Nov 1, 2008
Messages
55
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,430
Messages
5,636,227
Members
416,908
Latest member
Streetsweeper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top