Own Vehicle numbers using lookup.

[Shabbo]

Dear Sir,

I wanted help to find out list of our own vehicles from OWN VEHICLE sheet.

My Sheet3 Column A that is account head should show as ExpsFuel if Vehilces from our Own Vehicles and if not from Own Vehicles then vendor name from Entrybook should come.

Eg : I will apply lookup in Sheet3 Column A for Vehicle no : from Sheet3 Column B If that vehicle from Own Vehicles List then account head should show as ExpsFuel else vendor name from Entrybook Column B.

 

[vletm]

Shabbo
This would show those ... or how?

but Your sample result for
A2 ... why not pqr ltd?
(Entrybook has 10 times Vehno 4185 and two kind of Vendornames)
A3 ... why not pqr ltd?
(Entrybook has 5 times Vehno 4186 and two kind of Vendornames)

 

[Peter Bartholomew]

You can test whether a vehicle is one of your fleet by using MATCH to return a record number or #N/A. ISNA tests for the latter and allows you to use INDEX/MATCH or VLOOKUP on the other table.

= IF( ISNA(MATCH( [@[VehicleNo.]], OwnVeh[Veh.No.], 0 ) ),
INDEX( VehEntry[Vendorname], MATCH( [@[VehicleNo.]], VehEntry[Vehicle], 0 ) ),
"ExpsFuel" )

 

[Shabbo]

Dear Sir,
It gives exact result What I wanted to get but can you please give me some explanation to understand this formula so I can try this formula for my other accounting workbook as well.

 

[Peter Bartholomew]

Shabbo
Firstly, the data is held as tables so structured references are used. A complete column is referenced by using the heading within square brackets, e.g.
OwnVeh[Veh.No.]
If the reference is to data held in a different table the expression is preceded by the table name. To reference a field in the current record an '@' is used to create a relative reference, so
[@[VehicleNo.]]
is the single value within the current record that is going to be looked up elsewhere.

Turning to the workbook:
Column1 (Spreadsheet column D) tries to find the vehicle within the Own vehicles table
= MATCH( [@[VehicleNo.]], OwnVeh[Veh.No.], 0 )
Column2 wraps the result within ISNA() to identify fails
= ISNA(MATCH( [@[VehicleNo.]], OwnVeh[Veh.No.], 0 ) )
Column3 shows a further MATCH used to find the vehicle in the Entrybook
= MATCH( [@[VehicleNo.]], VehEntry[Vehicle], 0 )
and contains the record number. That number is used in Column4 to return the vendor name. The final IF returns the vendor name where it is available and "ExpsFuel" otherwise.

= IF( ISNA(MATCH( [@[VehicleNo.]], OwnVeh[Veh.No.], 0 ) ),
INDEX( VehEntry[Vendorname], MATCH( [@[VehicleNo.]], VehEntry[Vehicle], 0 ) ),
"ExpsFuel" )

I hope this helps.