Vlookup or Index/Match

  • Thread starter Thread starter Ram
  • Start date Start date
R

Ram

How can I lookup when match has more than one value..

Server1 C:\ (2) 21478666240 17488568534 3990097706 81.42297263
Server1 D:\ (3) 2.9362E+11 68383018334 2.25237E+11 23.28963077
Server1 E:\ (4) 1.25325E+11 48705869576 76619192568 38.8636309
with the basic syntax of the Vlookup or Index Match, I can get the
utilization of the C drive or E drive but not the D drive..which has a value
of 23.289 % Any help is much appreciated.
Thankyou
 
Maybe what you're after is to match based on more than a single criteria?
(you should always post your formula(s) attempted)

Presuming your source data as posted in cols A to G,
where col G contains the "%" figures you want returned
based on a twin criteria match of the server and drive in cols A and B

You could put this in say, I1, normal ENTER:
=INDEX(G1:G100,MATCH(1,INDEX((A1:A100="Server1")*(B1:B100="D:\"),),0))
which will return the required: 23.29 from col G
Adapt to suit. The match criteria can be easily expanded likewise in the
manner shown to include other params in other source cols if needed.

Aloha? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Focusing on this line ..
How can I lookup when match has more than one value ..
Click to expand...

Here's one way using a simple & fast formula set-up to extract multiple
matches
Presuming your source data as posted in cols A to G from row1 down
where col G contains the "%" figures you want returned
based on a twin criteria match of the server and drive in cols A and B

To extract all the values for "Server1" in col A, "D:\" in col B
In K1: =IF(AND(A1="Server1",B1="D:\"),ROW(),"")
In L1: =IF(ROW()>COUNT(K:K),"",INDEX(G:G,SMALL(K:K,ROW())))
Copy K1:L1 down to cover the max expected extent of source data, say down to
L100? Col L will return all the multiple match results neatly packed at the
top. Minimize/hide away col K, which is the criteria col. Its quite easy to
understand what's happening in the criteria col, so you could easily
cross-apply to handle whatever criteria in other situations.

voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
 
Hi Max,
Thankyou for the prompt reply, this did work need to tweak a bit..

INDEX(disk!F4:F57,MATCH(1,INDEX((disk!A4:A57="Server1")*(disk!B4:B57="D:\
(3)"),),0))
I am yet to try your other solution..will try it too..
Max Thanks a lot.
 
Back
Top