Data Lookup With Multiple Criteria

[muhammad susanto]

hello all..

i want to lookup data with criteria below, how to use vlookup or either..

[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]Criterias[/TH]
[TH]Score[/TH]
[/TR]
</thead><tbody> [TR]
[TD]Good[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Bad[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Worst[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Enough[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]lookup[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]given/data[/TD]
[TD]Expected Result[/TD]
[/TR]
[TR]
[TD]Bad[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Good[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]etc..[/TD]
[/TR]
</tbody>[/TABLE]

thanks in advance
.sst

 

[muhammad susanto]

sorry...it's possible using formula
=vlookup(H2;{Good;Bad;Worst;Enough};..............

 

[muhammad susanto]

i want, the formula's model like this
=VLOOKUP(A2;"Good";"Bad";"Worst";"Enough";{1;2;3;4})

explain :
Good instead 1
Bad -------- 2
Worst -------3
Enough ------4

It's possible?

 

[Marcelo Branco]

This?

=VLOOKUP(A2,{"Good",1;"Bad",2;"Worst",3;"Enough",4},2,0)

M.

 

[muhammad susanto]

yes...marcelo but after test your formula is not work, the show error #Ref !

 

[Marcelo Branco]

Worked for me


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

given/data​

[/td][td]

Expected Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Bad​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Good​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

enough​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

worst​

[/td][td]

3​

[/td][/tr]
[/table]

Formula in B2 copied down
=VLOOKUP(A2,{"Good",1;"Bad",2;"Worst",3;"Enough",4},2,0)

Observe the separators in the matrix
, (comma) is column separator
; (semicolon) is row separator

M.

 

[muhammad susanto]

hello Marcelo....brilliant!! thank you so much!!!!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Cryssy]

Having a similar problem. Any help is appreciated. Please help!

Vlookup is returning #n/a, I think because I have it looking up a cell that is the result of a formula. *Note: Table for Customers is different table than List of Zips and rate, if that's relevant.*

Ex.


A1 = customer (Selected from drop down)
A2 = Zip Code (auto generated from customer selected from A1)
A3 = Rate (based on zip populated in A2).


#N/A comes up in cell A3. When I manually type in the zip it works fine. If I choose True - it goes wrong rate. False is a must, but returns #N/A.

A1 = Bob's Cleaning
A2 = 12345
A3 = $750

 

[Cryssy]

Sorry. I forgot to add formula for A3: VLOOKUP(A3,van,2,FALSE) VAN is the table for rates.