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

O

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
Office Version
  1. 2019
Platform
  1. Windows
Hello,

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


[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl64, width: 64, align: right"]1[/TD]
[TD="class: xl64, width: 64, align: right"]2[/TD]
[TD="class: xl64, width: 64, align: right"]3[/TD]
[TD="class: xl64, width: 64, align: right"]4[/TD]
[TD="class: xl64, width: 64, align: right"]5[/TD]
[TD="class: xl64, width: 64, align: right"]6[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63"]alpha[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]43[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]bravo[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]charlie[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl63"]delta[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]echo[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]foxtrot[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"]golf[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]90[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63"]hotel[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]


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..
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Hi,

Assuming your data is in A1:H9, try:

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

Matty
 
Upvote 0
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
[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl64, width: 64, align: right"]1[/TD]
[TD="class: xl64, width: 64, align: right"]2[/TD]
[TD="class: xl64, width: 64, align: right"]3[/TD]
[TD="class: xl64, width: 64, align: right"]4[/TD]
[TD="class: xl64, width: 64, align: right"]5[/TD]
[TD="class: xl64, width: 64, align: right"]6[/TD]
[TD="class: xl64, width: 64, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"]alpha[/TD]
[TD="class: xl63, align: right"]100[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]43[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]bravo[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]charlie[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl63"]delta[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]echo[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]foxtrot[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"]golf[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]90[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63"]hotel[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]


Will appreciate further assistance..

Note: The data begins with the first row.
 
Upvote 0
Thanks Matty..

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

Matty said:
Hi,

Assuming your data is in A1:H9, try:

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

Matty
Click to expand...
 
Upvote 0
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?
 
Upvote 0
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.

Aladin Akyurek said:
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?
Click to expand...

Aladin Akyurek said:
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?
Click to expand...
 
Upvote 0
omairhe said:
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.
Click to expand...

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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Using COUNTIFS to Rank Within Nested IF, THEN
Replies
4
Views
562
diderooy
D
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
297
Peter_SSs
Peter_SSs
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
502
mumps
mumps
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
E
  • Question Question
If statement
Replies
4
Views
120
xlhelp15
xlhelp15

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top