vlookup rates without table , need formula

[makinmomb]

GOMA 480000 250000
KASUMBALESA 480000 250000
KITWE 420000 200000
LUBUMBASHI 480000 250000
LUSAKA 420000 200000
NDOLA 420000 200000

These is the table of rates

I need one formula two pick those rates on b1 and c1 where a1 is location
I dont need this table anywhere else , i need one formula to take
that table , some kind of multipe if lookup

 

[Aladin Akyurek]

Looks like...

=VLOOKUP(A1,{"GOMA",480000,250000;"KASUMBALESA",480000,250000;"KITWE",420000,200000;"LUBUMBASHI",480000,250000;"LUSAKA",420000,200000;"NDOLA",420000,200000},2,0)

=VLOOKUP(A1,{"GOMA",480000,250000;"KASUMBALESA",480000,250000;"KITWE",420000,200000;"LUBUMBASHI",480000,250000;"LUSAKA",420000,200000;"NDOLA",420000,200000},3,0)

where A1 houses a location like KITWE.

 

[makinmomb]

reads [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

reads [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

No, it wouldn't.

Let A2 = KITWE.

In B2 enter:

=VLOOKUP(A2,{"GOMA",480000,250000;"KASUMBALESA",480000,250000;"KITWE",420000,200000;"LUBUMBASHI",480000,250000;"LUSAKA",420000,200000;"NDOLA",420000,200000},2,0)

You'll get 420000.

 

[makinmomb]

Not working , same thingi , anyone else , with likely a multiple if

 

[Aladin Akyurek]

Not working , same thingi , anyone else , with likely a multiple if

What do you get?

 

[shg]

Works fine here. You may need to adjust argument separators for your language.

 

[strooman]

Works fine here. You may need to adjust argument separators for your language.

That could be the issue.

Formula from Aladin just works fine for me when I use the backslash as column-separator:

=VLOOKUP(A1;{"GOMA"\480000\250000;"KASUMBALESA"\480000\250000;"KITWE"\420000\200000;"LUBUMBASHI"\480000\250000;"LUSAKA"\420000\200000;"NDOLA"\420000\200000};2;0)

To determine the current separator settings run this code:

Sub ArraySeparators()
  Dim strsep As String
    strsep = "Alternate Array Separator =" & _
    Application.International(xlAlternateArraySeparator) & vbCrLf
    strsep = strsep & "Column Separator =" & _
    Application.International(xlColumnSeparator) & vbCrLf
    strsep = strsep & "Decimal Separator =" & _
    Application.International(xlDecimalSeparator) & vbCrLf
    strsep = strsep & "List Separator =" & _
    Application.International(xlListSeparator) & vbCrLf
    strsep = strsep & "Row Separator =" & _
    Application.International(xlRowSeparator) & vbCrLf
    strsep = strsep & "Thousands Separator =" & _
    Application.International(xlThousandsSeparator) & vbCrLf
  MsgBox (strsep)
End Sub

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	Kitwe	420000			GOMA	480000
2		420000			KASUMBALESA	480000
3					KITWE	420000
4		420000			LUBUMBASHI	480000
5					LUSAKA	420000
6					NDOLA	420000
9a

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,E1:F6,2,0)
B2	=VLOOKUP(A1,{"GOMA",480000;"KASUMBALESA",480000;"KITWE",420000;"LUBUMBASHI",480000;"LUSAKA",420000;"NDOLA",420000},2,0)
B4	=OR(A1={"Goma","Kasumbalesa","Lubumbashi"})*480000+OR(A1={"Kitwe","Lusaka","Ndola"})*420000

1. Put your lookup information into a Table
2. Try the Vlookup formula see B1.
3. Copy the formula in B1 to B2.
4. Highlight the Lookup table's range such as E1:F6; press F9
B2 should yield the self-contained Vlookup for your region.
5. B4 is another approach

 

[makinmomb]

I am using excel 2013 , anyone can guess which separator will work , i don't know how
to determine through code