Choosing the correct formula

[Potgieterloraine]

Good day. I need help please to choose the correct formula to use.

I want to use a vlookup formula to pull through a specific value from a specific column on another sheet but there are multiple arrays (named ranges) in one row.

What I am trying to do. On the sheet below in K12 I want to pull through the value in G4 on another sheet (pasted below). The problem is columns are deleted or added on the 2nd sheet so I want to compare all the ranges except for doing a normal vlookup to only 1 named range. The 2nd sheet contains 18 named ranges that I want to include in the formula.
The same formula will then apply to pull through the value of H4 on the 2nd sheet into L12 on the first sheet and then again in M12 as well.

This is the sheet where I want to pull the data from. There are 18 name ranges in row 4 that I want to include in the formula.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4				HM005			45	0	0		HM007			46	1	7
5
6
7
8
9	HM005		45	0	0
10	HM007		46	1	7
11
Lists

Cell Formulas
Range	Formula
C9:E10	C9	=INDEX(G$4:Q$4,MATCH($A9,$D$4:$L$4,0))

 

[Potgieterloraine]

=INDEX(G$4:Q$4,MATCH($A9,$D$4:$L$4,0))

Good day

Thank you for the assistance and the reply.

This formula works but can I apply it to look for one value over multiple ranges?

 

[Fluff]

In what way?

 

[Potgieterloraine]

In what way?

I have 2 sheets. I want to pull the values from the 2nd sheet into the 1st sheet

This is Sheet 2. In total there are 18 employees ( I am only using 2 for this example).

D5 and K5 is the matching indicators. I want to pull the value of G5 from the 2nd sheet to sheet 1 in K12. H5 on sheet 2 must go to L12 on sheet 1 and I5 on sheet 2 must go to M12. The problem is on the 2nd sheet employees are added and removed so HM005 might not always be in C:I on the 2nd sheet therefore I need to ad a formula to search for the information anywhere on sheet2.

This is sheet 1

 

[Fluff]

But that's what the formula does.

 

[Potgieterloraine]

But that's what the formula does.

I have missed something in my formula. I re-entered it and it is working 100%. Thank you for the help.

 

[Fluff]

Glad to help & thanks for the feedback.