What do i need to do to have exact match

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Nov 4, 2003

• #2

Try --

=INDEX(Distress!O:O,MATCH($A$6,Distress!$D:$D,0),0)

or --

=VLOOKUP($A$6,Distress!$D:$O,12,0)

To get a "0" return when <> match, --

=IF(ISNUMBER(MATCH($A$6,Distress!$D:$D,0)),VLOOKUP($A$6,Distress!$D:$O,12,0),0)

If you are going to be doing a bunch of these, or if you want to turbo-charge your Excel, use SETV/GETV from the free morefunc add-in, source available here on the board.

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Nov 4, 2003

• #3

To get the results (in blue) using the lookup values (in pink) and the table array (in yellow) compute the intermediate results (in green)...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	cat	cat	1	1		cat	1
3	horse	dog	2	0		dog	2
4	bird	#N/A	#N/A	0		mouse	3
5
6
7
Sheet3

 

Upvote 0

BlackSwan

New Member

Joined

Oct 27, 2002

Messages

35

• Nov 4, 2003

• #4

where do i download getv/setv?

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Nov 4, 2003

• #5

You don't need GETV/SETV if you use a worksheet cells to store intermediate results. The columns containing intermediate results can also be hidden.

 

Upvote 0

BlackSwan

New Member

Joined

Oct 27, 2002

Messages

35

• Nov 4, 2003

• #6

mark, what if on colum F and G has multiple cats

cat 1
cat 2
cat 3
cat 4

will cell D2 give me maximum value (4) ?

and what button do i have to press again to get {= } .. im an excel noob

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Nov 4, 2003

• #7

Excel's lookup functions (MATCH, VLOOKUP, LOOKUP, etc.) will return the 1st matching entry (if present). Why don't you extract the entries with max values to create a separate table array for use with these functions?

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Nov 4, 2003

• #8

BlackSwan said:

...and what button do i have to press again to get {= } ...

Click to expand...

Control+Shift+Enter

See the Excel Help topic for "Create an array formula".

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Nov 4, 2003

• #9

BlackSwan said:

mark, what if on colum F and G has multiple cats

cat 1
cat 2
cat 3
cat 4

will cell D2 give me maximum value (4) ?

and what button do i have to press again to get {= } .. im an excel noob

Click to expand...

See the below --

Book1
	A	B	C	D
1	cat	1	cat
2	cat	5
3	cat	9
4	cat	4	9
Sheet1

In C4: =MAX(IF(A1:A4=C1,B1:B4),0)

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Nov 4, 2003

• #10

And, getting back to the context of performing lookups what if you don't know how many "cat" entries there are (or their cell addresses) in the list beforehand?

 

Upvote 0