Looking up a value based on row and column

[DavidPonnet]

I am currently working on a big project, and need to fill in the pricing schedule.

In table 1, I would like an easy way to fill in the question marks. I have over 150 "cars" to fill in, so I am looking for the best solution to get this automated. I am used to work with Vlookup, but I have over 250 columns in my table 2 and would like to look on name instead of column number

[TABLE="width: 500"]
<tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car 1[/TD]
[TD]€[/TD]
[TD][/TD]
[TD][/TD]
[TD]Glass[/TD]
[TD]Light[/TD]
[TD]Locks[/TD]
[TD]Wheel[/TD]
[/TR]
[TR]
[TD]Glass[/TD]
[TD]???[/TD]
[TD][/TD]
[TD]Car 1[/TD]
[TD]250
[/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Light[/TD]
[TD]???[/TD]
[TD][/TD]
[TD]Car 2[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]200[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Locks[/TD]
[TD]???[/TD]
[TD][/TD]
[TD]Car 3[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]200[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Glass[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Light[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Locks[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wheels[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Glass[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Locks[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wheels[/TD]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Let A:B of Sheet1 house table 1; A:E of Sheet2 table 2.

In B2 of Sheet1 enter and copy down:

=VLOOKUP($A$1,Sheet2!E:F,MATCH($A2,INDEX(Sheet2!A:E,1,0),0),0)

 

[DavidPonnet]

Let A:B of Sheet1 house table 1; A:E of Sheet2 table 2.

In B2 of Sheet1 enter and copy down:

=VLOOKUP($A$1,Sheet2!E:F,MATCH($A2,INDEX(Sheet2!A:E,1,0),0),0)

Thank you for your reply. I a getting an #NA message atm, and not sure how to look for my mistake. (having 2 seperate files instead of tabs makes it even harder)

could you elaborate more on what you have done with the match and index formulas ? (I am quite familiar with Vlookup)

And what are all the 0 and 1 numbers at the end of the line mean ?

 

[DavidPonnet]

Test.xlsx

Ive uploaded a testfile on my dropbox (with both in the same tab, but if you could make it work, i think I might get it to work for me )

 

[Peter_SSs]

It would be easier if the 'headings' (Car 1, Car 2 etc) in the left column of Table 1 were identified as 'headings' in some way.

Never-the-less, for the layout in post no. 1, try this formula copied to the other relevant cells in column B.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Table 1			Table 2
2	Car 1				Glass	Light	Locks	Wheels
3	Glass	250		Car 1	250	50	200
4	Light	50		Car 2	150	150	200	500
5	Locks	200		Car 3	50		200	500
6
7	Car 2
8	Glass	150
9	Light	150
10	Locks	200
11	Wheels	500
12
13	Car 3
14	Glass	50
15	Locks	200
16	Wheels	500
INDEX

 

[Aladin Akyurek]

Thank you for your reply. I a getting an #NA message atm, and not sure how to look for my mistake. (having 2 seperate files instead of tabs makes it even harder)

could you elaborate more on what you have done with the match and index formulas ? (I am quite familiar with Vlookup)

And what are all the 0 and 1 numbers at the end of the line mean ?

Test.xlsx

Ive uploaded a testfile on my dropbox (with both in the same tab, but if you could make it work, i think I might get it to work for me )

The header row in D:H is 3, not 1. Hence:

=VLOOKUP($A$4,D:H,MATCH($A6,INDEX(D:H,3,0),0),0)

 

[DavidPonnet]

thank, its working now !

 

[Aladin Akyurek]

thank, its working now !

You are welcome.

 

[Sandie1712]

Problem here is i am not getting the value in " Wheel"

[TABLE="width: 1638"]
<colgroup><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Glass[/TD]
[TD]Light[/TD]
[TD]Locks[/TD]
[TD]Wheel[/TD]
[/TR]
[TR]
[TD]Car 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Car 1[/TD]
[TD]250[/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]Car 2[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]200[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Glass[/TD]
[TD]=VLOOKUP($A$4,D:H,MATCH($A6,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD]Car 3[/TD]
[TD]50[/TD]
[TD] [/TD]
[TD]200[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Light[/TD]
[TD]=VLOOKUP($A$4,D:H,MATCH($A7,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Locks[/TD]
[TD]=VLOOKUP($A$4,D:H,MATCH($A8,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Glass[/TD]
[TD]=VLOOKUP($A$10,D:H,MATCH($A11,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Light[/TD]
[TD]=VLOOKUP($A$10,D:H,MATCH($A12,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Locks[/TD]
[TD]=VLOOKUP($A$10,D:H,MATCH($A13,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Wheels[/TD]
[TD]=VLOOKUP($A$10,D:H,MATCH($A14,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Glass[/TD]
[TD]=VLOOKUP($A$4,D:H,MATCH($A17,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Locks[/TD]
[TD]=VLOOKUP($A$4,D:H,MATCH($A18,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Wheels[/TD]
[TD]=VLOOKUP($A$4,D:H,MATCH($A19,INDEX(D:H,3,0),0),0)[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Problem here is i am not getting the value in " Wheel"

The header contains Wheel, not Wheels, the look up value.

Change the header value of Wheel to Wheels or the look up value of Wheels to Wheel.