Looking for an Hlookup and Vlookup criss cross combined formula...

[omairhe]

Hello,

I have data in my Sheet that look like this...

	1	2	3	4	5	6	7
alpha	100	1	1	43	22	1	1
bravo	2	2	2	2	2	2	2
charlie	3	3	3	3	3	3	35
delta	4	4	4	4	4	4	4
echo	5	5	5	5	5	5	5
foxtrot	6	6	6	6	6	6	6
golf	7	7	7	90	7	7	7
hotel	8	8	8	8	8	8	8

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

in cell L1 = 4 (user input)
in cell L2 = golf (user input)
in cell L3 = 90 (formula result)

So what formula in cell ref: L3 so that the resulting value is 90

Thank you..

 

[Vbagreenhorn1]

try to combine the index and match formulas!

 

[Matty]

Hi,

Assuming your data is in A1:H9, try:

=INDEX(B2:H9,MATCH(L2,A2:A9,0),MATCH(L1,B1:H1,0))

Matty

 

[omairhe]

I know how to use an index and match formula but I cannot seem to incorporate that in my current scenario.. I could manage this much thus far with a guide on youtube on how to do this...

=VLOOKUP($L2,$A$3:$H$10,HLOOKUP($L$1,$B$1:$H$2,2,FALSE),FALSE)

But the problem with this is that I don't need double headers.
1st for the numerical headings.
and 2nd for the column no. (unwanted one)

Column A_Column B_Colmn C_Colmn D_Colmn E_Colmn F_Colmn G_Colmn H

	1	2	3	4	5	6	7
	2	3	4	5	6	7	8
alpha	100	1	1	43	22	1	1
bravo	2	2	2	2	2	2	2
charlie	3	3	3	3	3	3	35
delta	4	4	4	4	4	4	4
echo	5	5	5	5	5	5	5
foxtrot	6	6	6	6	6	6	6
golf	7	7	7	90	7	7	7
hotel	8	8	8	8	8	8	8

<tbody>
</tbody>

Will appreciate further assistance..

Note: The data begins with the first row.

 

[omairhe]

Thanks Matty..

I just give it a try and it solved the issue..

Hi,

Assuming your data is in A1:H9, try:

=INDEX(B2:H9,MATCH(L2,A2:A9,0),MATCH(L1,B1:H1,0))

Matty

 

[Aladin Akyurek]

Hard to follow?

What is the value of L2?

What is the value of L1?

What is the data?

What result do you expect for L1 and L2 together?

 

[omairhe]

Hello Aladin,

L1 are the dates, whereas
L2 is the name of a product sold on that particular date.

I was hoping to shy away from the details as it may only complicate the requirement. I still have a long way to go with my current excel project, hence I am figuring out what goes where and trying to put together the pieces. Thank you for asking.

Hard to follow?

What is the value of L2?

What is the value of L1?

What is the data?

What result do you expect for L1 and L2 together?

Hard to follow?

What is the value of L2?

What is the value of L1?

What is the data?

What result do you expect for L1 and L2 together?

 

[Aladin Akyurek]

Hello Aladin,

L1 are the dates, whereas
L2 is the name of a product sold on that particular date.

I was hoping to shy away from the details as it may only complicate the requirement. I still have a long way to go with my current excel project, hence I am figuring out what goes where and trying to put together the pieces. Thank you for asking.

Suppose that (1) we have the data in A:F.

Suppose also that (2) we have dates in the first row of A:F.

Suppose again that we have products in column A of A:F.

If you want the data from the intersection names and dates, try:

=VLOOKUP(L2,A:F,MATCH(L1,INDEX(A:F,1,0),0),0)

 

[omairhe]

That is wonderful, Aladin.
Thanks again..

 

[Aladin Akyurek]

That is wonderful, Aladin.
Thanks again..

You are welcome.