Sep 11 2021 02:01 PM
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
Sep 11 2021 02:34 PM - edited Sep 11 2021 02:36 PM
See the attached version. The formula in B2 is
=WENNFEHLER(SVERWEIS(A2;park!$A$2:$B$3;2;FALSCH);"")
Sep 12 2021 02:41 AM
@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.
Sep 12 2021 04:11 AM
There are two problems:
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
Sep 13 2021 01:47 AM
Sep 13 2021 02:37 AM
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.
Sep 13 2021 10:57 AM
@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
Sep 13 2021 11:18 AM
Can you explain how you arrive at 45,10 for BER SOF and at 55,10 for BER HRG?
Sep 13 2021 12:28 PM
Sep 13 2021 01:12 PM
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.
Sep 14 2021 09:46 AM
@Hans Vogelaar
Hi, i created an table with predefined routes and taxes for them.
Now it should work.
Thx
Sep 14 2021 09:55 AM
A simple VLOOKUP / SVERWEIS will work.
Sep 15 2021 12:48 AM
SolutionSep 15 2021 01:59 AM
Sep 15 2021 02:25 AM
I used
=IFERROR(VLOOKUP(A2&"-"&C2,'pax tax'!A:C,3,FALSE),0)
=WENNFEHLER(SVERWEIS(A2&"-"&C2;'pax tax'!A:C;3;FALSCH);0)
Sep 21 2021 12:23 PM
Sep 21 2021 01:33 PM
I'm afraid I don't understand. Could you attach a sample workbook and explain in detail what you want?
Sep 21 2021 11:01 PM
@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
Sep 22 2021 02:51 AM
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.
Sep 15 2021 12:48 AM
Solution