formula needed for VLOOKUP

[MirDia1985]

Good evening, I am looking for an VLOOPUP formula or other formula, which check two base (BER and LEJ) from table in whole table on various columns and the dedicated price for the base from another sheet and should automatic fill in the correct figure in the fieled park in sheet "base". 
I have attached the excel table. Thank you in advance

[#2]

@MirDia1985 

See the attached version. The formula in B2 is

=WENNFEHLER(SVERWEIS(A2;park!$A$2:$B$3;2;FALSCH);"")

[#3]

@Hans Vogelaar 

Hi, thank you. I have tried for another table, see attachment. 
The formula should be from FL program and Expenses per Week.
In the column Parking should look up from Column "D" if DUS or LEJ and check price from sheet "Expenses per Week" from A3 to B4, Like if DUS than 888 and if LEJ than 999 should automatically be display at the column in "Parking" in sheet FL program. 

Looking forward

P.S. Can then i just copy free to another tables and what should i take attention to.

[#4]

@MirDia1985 

There are two problems:

1. Cell A4 on the EXPENSES sheet contains a space after DUS: "DUS " instead of "DUS". This prevents the VLOOKUP formula from finding a match for "DUS". Remove that space.
2. There are spaces at the end of the name of the EXPENSES sheet: it is "EXPENSES_PER_WEEK   " instead of "EXPENSES_PER_WEEK". Remove those spaces from the name of the sheet and from the VLOOKUP formulas.

Also, I'd use $A$3:$B$4 as lookup range. That way, you can copy the formula up or down and the range will stay the same.

After making the changes in 1. and 2. above, Select AH7:AH53 on FL_Program.

Enter the formula

=IFERROR(VLOOKUP(D7,EXPENSES_PER_WEEK!$A$3:$B$4,2,FALSE),"")

In German:

=WENNFEHLER(SVERWEIS(D7;EXPENSES_PER_WEEK!$A$3:$B$4;2;FALSCH);"")

and press Ctrl+Enter to populate all selected cells. Excel will automatically adjust D7 to D8 ... D53 in the rows below, while keeping $A$3:$B$4 the same.

Another problem: the formulas in column S perform a lookup of the currency in column R in R1:S2. But R1 and R2 contain 111 and 110, not USD or similar. I don't know what you intended there.

See the attached version.

P.S. M1 contained a #REF! error so I entered 1,18

[#5]

Hi, thanks a lot for ur cooperation.
Is it possible to create a formula which uses VLOOKUP from differnt excel file?
Thank you

[#6]

@MirDia1985 

Yes. that is possible. Here are the steps:

Open both the workbook with the lookup range and the workbook where you want the formula.

Select the cell where you want the formula.

Start typing the formula, for example

=SVERWEIS(A2;

Now use the mouse or the keyboard to switch to the other workbook.

Activate the sheet with the lookup range.

Select the lookup range. You will see something like

=SVERWEIS(A2;'[ExterneDatei.xlsx]Blatt 2'!$A$2:$B$100

Complete the formula the normal way:

=SVERWEIS(A2;'[ExterneDatei.xlsx]Blatt 2'!$A$2:$B$100;2;FALSCH)

Press Enter and check whether it returns the expected result.

If it does, switch to the other workbook and close it. The formula will now include the path of the other workbook:

=SVERWEIS(A2;'C:\Excel\[ExterneDatei.xlsx]Blatt 2'!$A$2:$B$100;2;FALSCH)

Save the workbook.

[#7]

@Hans Vogelaar 

Hi, attached one file with sheets "program" and "taxes".
I have filled in automatically in column taxes the figures according to requirements of destination and origin. Is it possible to create such VLOOKUP?

Like e.g. once again:

BER SOF -> pax fees EU  (45,10 eur) from sheet "taxes" vlookup
BER HRG -> pax fees ex EU (55,10 eur) from sheet "taxes" vlookup

All according to rules.

Thank you

 

[#8]

@MirDia1985 

Can you explain how you arrive at 45,10 for BER SOF and at 55,10 for BER HRG?

[#9]

Hi, the figures are predefined.
In regards of BER SOF and price, the system has to check "BER" and "SOF". The BER is in EU as start and SOF as destination is also in EU and due to this comes to 45,10 eur.
For the BER HRG, the destination of HRG is not in EU as destination, and thatfore ex BER (start) the price for non EU is 55,10 eur.
Best regards

[#10]

@MirDia1985 

How can the formula "know" that BER is in EU and LAX is not? I know that and you know that, but Excel doesn't. Both have EU and ex EU fees.

[#11]

@Hans Vogelaar 

Hi, i created an table with predefined routes and taxes for them.
Now it should work.
Thx

[#12]

@MirDia1985 

A simple VLOOKUP / SVERWEIS will work.

[#13 · Accepted solution]

Hi, is it possible to lookup for price only in column C, but to search in columns A and C, but exclude column B. Thx

[#14]

@MirDia1985 

[#15]

Hi, can u pls send me formula. Should be like the one you send last time, but with the exclude. Thx

[#16]

@MirDia1985 

I used

=IFERROR(VLOOKUP(A2&"-"&C2,'pax tax'!A:C,3,FALSE),0)

=WENNFEHLER(SVERWEIS(A2&"-"&C2;'pax tax'!A:C;3;FALSCH);0)

[#17]

Hi, is there any possibilty to check as well from another excel table, to use the formula only and place figure if currency in dedicated column in in EUR, if not than figure 0.

Of course than we have to make sure, that the currency in column is set up to currency EURO from settings.

Thx

[#18]

@MirDia1985 

I'm afraid I don't understand. Could you attach a sample workbook and explain in detail what you want?

[#19]

@Hans Vogelaar 
Hi, thank you. Pls see attached files. I have entered in both data. the data in buc estimate excel has to vlookup from buc taxes (figures) and currency and place in dedicated column. So the formula should either place 0 as figure or the dedicated figure from buc taxes. Hope you understand. Best

[#20]

@MirDia1985 

There was no way I could have guessed that from your previous post.

This is more complicated than you'd think, because it depends on the number format of the cells in the buc taxes sheet. We need VBA to read the number format, so the buc estimates workbook is now a .xlsm, and you'll have to allow macros when you open it. Also, the buc taxes workbook must be open in Excel for it to work.