How to apply an IF Vlookups to this scenario

[mageeg]

HI, i don't know if this is possible but thought i'd ask.

I have created an excel database, one of the datasheets look like this:

I then have a data entry form, in which the user enters data which then is submitted to a database sheet called "Database" which looks like this:

Is it possible to have some sort of IF Vlookup in this database, that would be in a row called certificate corresponding to the certificate column in the above table.
It would basically say, if BatchNo = '' then display the certificate from the same row as that Batch number.

For example, if the Batch Number in the database = 339925, then at the end of the row display the certificate corresponding to that batch number.

Hope that is clear, any help would be greatly appreciated

 

[rondeondo]

Hi mageeg
There is nothing in your second table that matches the first table.
If your batch number is blank then how would you choose the batch number to lookup?
I may be missing something.

 

[mageeg]

Hi mageeg
There is nothing in your second table that matches the first table.
If your batch number is blank then how would you choose the batch number to lookup?
I may be missing something.

Hi, thanks for getting back to me!

sorry, realised I screenshotted the wrong part of the database table, there is multiple tables included, I just needed help with this one table as it has a hyperlink to a PDF. This part of the database (batch number etc) is in the database but further across

 

[rondeondo]

Hi Mageeg
It's still unclear what it is you want to do. in your OP you said if the batch is blank then grab the pdf link for the same batch id, but the batch is blank... or did you mean if the certificate is blank go look up the batch?

 

[mageeg]

Hi Mageeg
It's still unclear what it is you want to do. in your OP you said if the batch is blank then grab the pdf link for the same batch id, but the batch is blank... or did you mean if the certificate is blank go look up the batch?

sorry, it's my fault i definitely wasn't clear. I've found some sort of way to do it whether it's the best way i'm not sure.

So this is the part of my database table that is necessary for this bit.

I've then told it to when user saves data on entry form, to add a yes to column S. I then use the following IF Hyperlink, to display the correct cert depending on the Batch number that was inputted by the User.

=IF(S2="yes",HYPERLINK(CONCATENATE("T:\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\Safety Valve Certs\",L2,".pdf"),"Safety Cert"),"")

Like i say, there may be a much better way of going about it, but this does do what i would like to do.

Now however, i would like to do the same thing for a Serial Number. The problem with this however, is that the certificate that is given to the serial number, depends what range it is in.

See following table:

As you can from the above table the certficate in column H, is = to Serial numbers between A - B

How can i edit the hyperlink used above for Batch number, to tell it to display the correct cert depending on whether the Serial Number is between the From and To range?

Hope i hade made myself clearer, sorry if not!

 

[rondeondo]

Hi mageeg
vlookup allow for searching through an ordered list and returning the value before or equal to the value you are looking for.
so, =vlookup(serialNumber, $a$1:$h$5, 8,true)
extend the range a1:h5 for however many rows you have, or use a:h to do the whole of the colums. The TRUE, tells it to not require an exact match so for a number in between two values it will return the one of lower value. The 8 tells it to return the 8th colum...