Picking a row from a range

smithrv

New member
Joined
Aug 3, 2013
Messages
2
Reaction score
0
Points
0
View attachment forum attachment.xlsx
I have a list of existing sizes L2:M50

I have a user input form: B2 and B3 (length, B4 doesn't pertain)

I want to be able to have a user enter dimensions in B2 and B3 and have the spreadsheet tell them whether that size exists in the data range L2:M50. This is a very small portion of the data range(for demonstration only). The full data range is 500 rows and growing.

I've tried VLOOKUP, and INDEX with MATCH, but haven't got it. I've also looked at TABLE.
Running EXCEL 2013

Any suggestions would be greatly appreciated, and thank you in advance.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Hi smithrv. Here's how I'd do it:

First, turn your lookup table into an Excel table. I use the keyboard shortcut Ctrl + T to do this.
Then in say cell C6 put "Number of matches"
Then in say cell C7 enter this formula:
=COUNTIFS(Table1[OD],B2,Table1[ID],B3)

By the way, calculation is set to manual in your file. I never do this...it's too risky.

Many people mistakenly think that having to run a spreadsheet in manual calculation mode is simply a byproduct of having a big spreadsheet with lots of data and formulas in it. But in actual fact in most cases I've seen, performance issues are more often than not because of inefficient spreadsheet design – compounded by so-called volatile functions such as OFFSET, INDIRECT, TODAY, NOW, and RAND – rather than simply being a by-product of big files.
 

smithrv

New member
Joined
Aug 3, 2013
Messages
2
Reaction score
0
Points
0
Outstanding, thanks so much Jeffrey. So funny, I was going to search my Excel books for countif tomorrow. Calculation set to manual-I just use default, thanks for the tip. I will have a another table, in the near future, that I can use this same formula on. Same format, different data.
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
No problem. Thanks for the feedback, and see you back here anytime :)
 
Top